New to SSIS so bear with me....

I have migrated a 2000 DTS package to a 2005 SSIS package. The migration
wizard converted all DTS tasks(20 tasks) to an "EXECUTE DTS 2000 PACKAGE
TASK" control flow item(20 control flow tasks). The task is a simple extract
from an Excel worsheet and load into a SQL 2005 database table.

I did not create the original DTS package and am noticing that even though
the source of all tasks is the same Excel file a new Data Source/Connection
Manager was created for each "read" from the same Excel file(Needless to say
you can imagine how many connection managers the package has). The migration
wizard of course re-created all the Data Sources/connection managers just
like they existed in DTS 2000. The wizard worked great from that standpoint.

Now the problem, I would like to clean up the SSIS package and basically
have all 20 Control Flow tasks reference the one Excel Data Source/Connection
Manager; however, when I edit the control flow task and get into the DTS 2000
Package Designer I am unable to select the same connection when I choose the
Existing Connection radio button. I hope this makes sense. The drop-down
box does not show me all connections to pick from.

Basically, I want to have one connection to Excel and have all twenty(20)
control flow tasks reference this one Data Source/Connection manager.

I am wondering if because it was migrated from DTS 2000 to SSIS 2005 I am
stuck with having to use all the different originally created connections
even though they all "point" to the same Source Excel file.

NOTE: I have the same problem with the destination. Multiple Connection
Managers all pointing to the same 2005 database table.

Any help would be greatly appreciated.

Dear Customer,

Thank you for contacting Microsoft Online Community Support. This is Mark,a
SQL Server Engineer. I'm glad to assist you with the issue.

Based on my understanding, you should like to share the Connection
Manager/Excel Data Source with all Control Flow tasks. Is it correct? If I
misunderstand anything, please tell me. That will help us better assist you
with the issue.

In order to resolve the issue, I would like to explain the following.
Explanation
=================
1) Since the DTS package is desinged by DTS 2000 package designer and in
the DTS 2000 package designer, the SQL 2005 object seems not be recognized,
the Connection Manager/Excel Data Source created in the SQL Server 2005
cann't be shared in DTS 2000 package designer.

2) The existing connection in the drop-down box shows the connection
created in the DTS 2000 package designer. The connection created in the SQL
Server 2005 Tool will not be listed in the drop-down box. Because SQL 2005
object is not able to recognized in SQL 2000 component.

Based on the current situation, I suggest to re-create the connection in
the DTS 2000 package designer manually.

If you have any questions or concerns, please let me know.

I look forward to your update.

Thanks.

Best regards,
Mark Han
Microsoft Online Community Support
================================================== =========
Delighting our customers is our #1 priority. We welcome your
comments and suggestions about how we can improve the
support we provide to you. Please feel free to let my manager
know what you think of the level of service provided. You can
send feedback directly to my manager at: msdnmg (AT) microsoft (DOT) com.
================================================== =========
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscripti...ult.aspx#notif
ications.

Note: The MSDN Managed Newsgroup support offering is for
non-urgent issues where an initial response from the community
or a Microsoft Support Engineer within 1 business day is acceptable.
Please note that each follow up response may take approximately
2 business days as the support professional working with you may
need further investigation to reach the most efficient resolution.
The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by
contacting Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscripti...t/default.aspx.
================================================== ==========
This posting is provided "AS IS" with no warranties, and confers no rights.
================================================== =======

Dear Customer,

Thank you for contacting Microsoft Online Community Support. This is Mark,a
SQL Server Engineer. I'm glad to assist you with the issue.

Based on my understanding, you should like to share the Connection
Manager/Excel Data Source with all Control Flow tasks. Is it correct? If I
misunderstand anything, please tell me. That will help us better assist you
with the issue.

In order to resolve the issue, I would like to explain the following.
Explanation
=================
1) Since the DTS package is desinged by DTS 2000 package designer and in
the DTS 2000 package designer, the SQL 2005 object seems not be recognized,
the Connection Manager/Excel Data Source created in the SQL Server 2005
cann't be shared in DTS 2000 package designer.

2) The existing connection in the drop-down box shows the connection
created in the DTS 2000 package designer. The connection created in the SQL
Server 2005 Tool will not be listed in the drop-down box. Because SQL 2005
object is not able to recognized in SQL 2000 component.

Based on the current situation, I suggest to re-create the connection in
the DTS 2000 package designer manually.

If you have any questions or concerns, please let me know.

I look forward to your update.

Thanks.

Best regards,
Mark Han
Microsoft Online Community Support
================================================== =========
Delighting our customers is our #1 priority. We welcome your
comments and suggestions about how we can improve the
support we provide to you. Please feel free to let my manager
know what you think of the level of service provided. You can
send feedback directly to my manager at: msdnmg (AT) microsoft (DOT) com.
================================================== =========
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscripti...ult.aspx#notif
ications.

Note: The MSDN Managed Newsgroup support offering is for
non-urgent issues where an initial response from the community
or a Microsoft Support Engineer within 1 business day is acceptable.
Please note that each follow up response may take approximately
2 business days as the support professional working with you may
need further investigation to reach the most efficient resolution.
The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by
contacting Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscripti...t/default.aspx.
================================================== ==========
This posting is provided "AS IS" with no warranties, and confers no rights.
================================================== =======

Dear Customer,

Thank you for contacting Microsoft Online Community Support. This is Mark,a
SQL Server Engineer. I'm glad to assist you with the issue.

Based on my understanding, you should like to share the Connection
Manager/Excel Data Source with all Control Flow tasks. Is it correct? If I
misunderstand anything, please tell me. That will help us better assist you
with the issue.

In order to resolve the issue, I would like to explain the following.
Explanation
=================
1) Since the DTS package is desinged by DTS 2000 package designer and in
the DTS 2000 package designer, the SQL 2005 object seems not be recognized,
the Connection Manager/Excel Data Source created in the SQL Server 2005
cann't be shared in DTS 2000 package designer.

2) The existing connection in the drop-down box shows the connection
created in the DTS 2000 package designer. The connection created in the SQL
Server 2005 Tool will not be listed in the drop-down box. Because SQL 2005
object is not able to recognized in SQL 2000 component.

Based on the current situation, I suggest to re-create the connection in
the DTS 2000 package designer manually.

If you have any questions or concerns, please let me know.

I look forward to your update.

Thanks.

Best regards,
Mark Han
Microsoft Online Community Support
================================================== =========
Delighting our customers is our #1 priority. We welcome your
comments and suggestions about how we can improve the
support we provide to you. Please feel free to let my manager
know what you think of the level of service provided. You can
send feedback directly to my manager at: msdnmg (AT) microsoft (DOT) com.
================================================== =========
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscripti...ult.aspx#notif
ications.

Note: The MSDN Managed Newsgroup support offering is for
non-urgent issues where an initial response from the community
or a Microsoft Support Engineer within 1 business day is acceptable.
Please note that each follow up response may take approximately
2 business days as the support professional working with you may
need further investigation to reach the most efficient resolution.
The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by
contacting Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscripti...t/default.aspx.
================================================== ==========
This posting is provided "AS IS" with no warranties, and confers no rights.
================================================== =======

Dear Customer,

Thank you for contacting Microsoft Online Community Support. This is Mark,a
SQL Server Engineer. I'm glad to assist you with the issue.

Based on my understanding, you should like to share the Connection
Manager/Excel Data Source with all Control Flow tasks. Is it correct? If I
misunderstand anything, please tell me. That will help us better assist you
with the issue.

In order to resolve the issue, I would like to explain the following.
Explanation
=================
1) Since the DTS package is desinged by DTS 2000 package designer and in
the DTS 2000 package designer, the SQL 2005 object seems not be recognized,
the Connection Manager/Excel Data Source created in the SQL Server 2005
cann't be shared in DTS 2000 package designer.

2) The existing connection in the drop-down box shows the connection
created in the DTS 2000 package designer. The connection created in the SQL
Server 2005 Tool will not be listed in the drop-down box. Because SQL 2005
object is not able to recognized in SQL 2000 component.

Based on the current situation, I suggest to re-create the connection in
the DTS 2000 package designer manually.

If you have any questions or concerns, please let me know.

I look forward to your update.

Thanks.

Best regards,
Mark Han
Microsoft Online Community Support
================================================== =========
Delighting our customers is our #1 priority. We welcome your
comments and suggestions about how we can improve the
support we provide to you. Please feel free to let my manager
know what you think of the level of service provided. You can
send feedback directly to my manager at: msdnmg (AT) microsoft (DOT) com.
================================================== =========
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscripti...ult.aspx#notif
ications.

Note: The MSDN Managed Newsgroup support offering is for
non-urgent issues where an initial response from the community
or a Microsoft Support Engineer within 1 business day is acceptable.
Please note that each follow up response may take approximately
2 business days as the support professional working with you may
need further investigation to reach the most efficient resolution.
The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by
contacting Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscripti...t/default.aspx.
================================================== ==========
This posting is provided "AS IS" with no warranties, and confers no rights.
================================================== =======

Dear Customer,

Thank you for contacting Microsoft Online Community Support. This is Mark,a
SQL Server Engineer. I'm glad to assist you with the issue.

Based on my understanding, you should like to share the Connection
Manager/Excel Data Source with all Control Flow tasks. Is it correct? If I
misunderstand anything, please tell me. That will help us better assist you
with the issue.

In order to resolve the issue, I would like to explain the following.
Explanation
=================
1) Since the DTS package is desinged by DTS 2000 package designer and in
the DTS 2000 package designer, the SQL 2005 object seems not be recognized,
the Connection Manager/Excel Data Source created in the SQL Server 2005
cann't be shared in DTS 2000 package designer.

2) The existing connection in the drop-down box shows the connection
created in the DTS 2000 package designer. The connection created in the SQL
Server 2005 Tool will not be listed in the drop-down box. Because SQL 2005
object is not able to recognized in SQL 2000 component.

Based on the current situation, I suggest to re-create the connection in
the DTS 2000 package designer manually.

If you have any questions or concerns, please let me know.

I look forward to your update.

Thanks.

Best regards,
Mark Han
Microsoft Online Community Support
================================================== =========
Delighting our customers is our #1 priority. We welcome your
comments and suggestions about how we can improve the
support we provide to you. Please feel free to let my manager
know what you think of the level of service provided. You can
send feedback directly to my manager at: msdnmg (AT) microsoft (DOT) com.
================================================== =========
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscripti...ult.aspx#notif
ications.

Note: The MSDN Managed Newsgroup support offering is for
non-urgent issues where an initial response from the community
or a Microsoft Support Engineer within 1 business day is acceptable.
Please note that each follow up response may take approximately
2 business days as the support professional working with you may
need further investigation to reach the most efficient resolution.
The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by
contacting Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscripti...t/default.aspx.
================================================== ==========
This posting is provided "AS IS" with no warranties, and confers no rights.
================================================== =======

Dear Customer,

Thank you for contacting Microsoft Online Community Support. This is Mark,a
SQL Server Engineer. I'm glad to assist you with the issue.

Based on my understanding, you should like to share the Connection
Manager/Excel Data Source with all Control Flow tasks. Is it correct? If I
misunderstand anything, please tell me. That will help us better assist you
with the issue.

In order to resolve the issue, I would like to explain the following.
Explanation
=================
1) Since the DTS package is desinged by DTS 2000 package designer and in
the DTS 2000 package designer, the SQL 2005 object seems not be recognized,
the Connection Manager/Excel Data Source created in the SQL Server 2005
cann't be shared in DTS 2000 package designer.

2) The existing connection in the drop-down box shows the connection
created in the DTS 2000 package designer. The connection created in the SQL
Server 2005 Tool will not be listed in the drop-down box. Because SQL 2005
object is not able to recognized in SQL 2000 component.

Based on the current situation, I suggest to re-create the connection in
the DTS 2000 package designer manually.

If you have any questions or concerns, please let me know.

I look forward to your update.

Thanks.

Best regards,
Mark Han
Microsoft Online Community Support
================================================== =========
Delighting our customers is our #1 priority. We welcome your
comments and suggestions about how we can improve the
support we provide to you. Please feel free to let my manager
know what you think of the level of service provided. You can
send feedback directly to my manager at: msdnmg (AT) microsoft (DOT) com.
================================================== =========
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscripti...ult.aspx#notif
ications.

Note: The MSDN Managed Newsgroup support offering is for
non-urgent issues where an initial response from the community
or a Microsoft Support Engineer within 1 business day is acceptable.
Please note that each follow up response may take approximately
2 business days as the support professional working with you may
need further investigation to reach the most efficient resolution.
The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by
contacting Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscripti...t/default.aspx.
================================================== ==========
This posting is provided "AS IS" with no warranties, and confers no rights.
================================================== =======

Dear Customer,

Thank you for contacting Microsoft Online Community Support. This is Mark,a
SQL Server Engineer. I'm glad to assist you with the issue.

Based on my understanding, you should like to share the Connection
Manager/Excel Data Source with all Control Flow tasks. Is it correct? If I
misunderstand anything, please tell me. That will help us better assist you
with the issue.

In order to resolve the issue, I would like to explain the following.
Explanation
=================
1) Since the DTS package is desinged by DTS 2000 package designer and in
the DTS 2000 package designer, the SQL 2005 object seems not be recognized,
the Connection Manager/Excel Data Source created in the SQL Server 2005
cann't be shared in DTS 2000 package designer.

2) The existing connection in the drop-down box shows the connection
created in the DTS 2000 package designer. The connection created in the SQL
Server 2005 Tool will not be listed in the drop-down box. Because SQL 2005
object is not able to recognized in SQL 2000 component.

Based on the current situation, I suggest to re-create the connection in
the DTS 2000 package designer manually.

If you have any questions or concerns, please let me know.

I look forward to your update.

Thanks.

Best regards,
Mark Han
Microsoft Online Community Support
================================================== =========
Delighting our customers is our #1 priority. We welcome your
comments and suggestions about how we can improve the
support we provide to you. Please feel free to let my manager
know what you think of the level of service provided. You can
send feedback directly to my manager at: msdnmg (AT) microsoft (DOT) com.
================================================== =========
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscripti...ult.aspx#notif
ications.

Note: The MSDN Managed Newsgroup support offering is for
non-urgent issues where an initial response from the community
or a Microsoft Support Engineer within 1 business day is acceptable.
Please note that each follow up response may take approximately
2 business days as the support professional working with you may
need further investigation to reach the most efficient resolution.
The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by
contacting Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscripti...t/default.aspx.
================================================== ==========
This posting is provided "AS IS" with no warranties, and confers no rights.
================================================== =======

Dear Customer,

Thank you for contacting Microsoft Online Community Support. This is Mark,a
SQL Server Engineer. I'm glad to assist you with the issue.

Based on my understanding, you should like to share the Connection
Manager/Excel Data Source with all Control Flow tasks. Is it correct? If I
misunderstand anything, please tell me. That will help us better assist you
with the issue.

In order to resolve the issue, I would like to explain the following.
Explanation
=================
1) Since the DTS package is desinged by DTS 2000 package designer and in
the DTS 2000 package designer, the SQL 2005 object seems not be recognized,
the Connection Manager/Excel Data Source created in the SQL Server 2005
cann't be shared in DTS 2000 package designer.

2) The existing connection in the drop-down box shows the connection
created in the DTS 2000 package designer. The connection created in the SQL
Server 2005 Tool will not be listed in the drop-down box. Because SQL 2005
object is not able to recognized in SQL 2000 component.

Based on the current situation, I suggest to re-create the connection in
the DTS 2000 package designer manually.

If you have any questions or concerns, please let me know.

I look forward to your update.

Thanks.

Best regards,
Mark Han
Microsoft Online Community Support
================================================== =========
Delighting our customers is our #1 priority. We welcome your
comments and suggestions about how we can improve the
support we provide to you. Please feel free to let my manager
know what you think of the level of service provided. You can
send feedback directly to my manager at: msdnmg (AT) microsoft (DOT) com.
================================================== =========
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscripti...ult.aspx#notif
ications.

Note: The MSDN Managed Newsgroup support offering is for
non-urgent issues where an initial response from the community
or a Microsoft Support Engineer within 1 business day is acceptable.
Please note that each follow up response may take approximately
2 business days as the support professional working with you may
need further investigation to reach the most efficient resolution.
The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by
contacting Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscripti...t/default.aspx.
================================================== ==========
This posting is provided "AS IS" with no warranties, and confers no rights.
================================================== =======

Thank you for your response.

First and foremost I have also noticed that their are MSDN Forums. What is
the difference between these(the one i am in now) discussion groups and the
MSDN Forums. Should I be posting my info. to the MSDN forums.

You are correct in the understanding. I want to share the Excel Data Source.

Explanation number 1 response.
I have created nothing in SQL 2005(yet) everything that exists was created
during the running of the Migration Wizard.

Understanding what you are saying, all of the Connection Mangers created in
the SSIS package(via the DTS to SSIS Migration Wizard) are not related to the
Data Sources that I see within each Control Flow DTS 2000 Package Task. Is
that correct?

If I create a new Excel Data Source in one of the Control Flow tasks(which
is basically a DTS 2000 Execution Task) should I /Can I use that Data Source
within the other 19 Control flow tasks or is each DTS 2000 Execution Task
unique. Meaning I have to have basically 20 unique data Sources and Data
Destinations.

If it helps I can send some screen shots. What is your e-mail or where can
I send it?

Thanks

"Mark Han[MSFT]" wrote: