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@microsoft.com.
===========================================================
Get notification to my posts through email? Please refer to 
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.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/subscriptions/support/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:

> 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@microsoft.com.
> ===========================================================
> Get notification to my posts through email? Please refer to 
> http://msdn.microsoft.com/subscriptions/managednewsgroups/default.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/subscriptions/support/default.aspx.
> ============================================================
> This posting is provided "AS IS" with no warranties, and confers no rights.
> =========================================================
> 
>

Now that you have cut your teeth on SSIS, it't time to chew on something a 
little more meaty:

Try re-creating everything from a pure SSIS package standpoint (not relying 
on 2000 DTS). I suggest you take this approach with a new, clean SSIS package:

Create one Connection Manager to an Excel file.
Create one Connection Manager to your SQL database.
Create and test one Data Flow task that moves data.
Once you have that and are satisfied that you know what you're doing, create 
a second Data Flow task for the next bit of data you want, but use the same 
two Connection Managers as before. 

It may take you a whole day to get the first Data Flow task to work properly 
but stick with it. SSIS is a good product and well worth investing some time 
into is basic use.

Keep us posted if you need help.
-- 
Todd C

Dear Customer,

In order to address your concern, i would like to explain the following
1 your understanding of my answer is correct.

2 to answer you question: if creating a new Excel Data Source in a DTS 
2000, the data source is not be able to be shared in the SSIS package also. 
Becuase the data source is created in the DTS 2000 package designer; and 
every SQL 2000 package excuted by SSIS task(excute DTS 2000 package) is 
independ on each other. For the "excute DTS 2000 package task", it uses 
another connection, not the same as the connection in DTS 2000 package; 

If anything is unclear or you need me explain further, please tell me.

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@microsoft.com.
=========================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
=========================================================

Thanks again for your response.

Basically if I understand, each task in the DTS 2000 package became a 
Control Flow item with an "Execute DTS 2000 Task" inside it (again, my 
original migration from DTS was 20 tasks became 20 unique control flow items 
within SSIS).  Because of this, the connections used within each task in the 
DTS 2000 package area not "shareable" across the control Flow items.

For example, if one task in the DTS package uses "CONN 1" and then got 
migrated to one of the Control Flow items during the DTS to SSIS migration, 
this connection can then not be used within the other Control Flow items.

Is all this correct?

What is getting me confused is the fact that all the connections(that were 
migrated) are showing in the Connection Manager pane within BIDS, yet they 
are not all available within each Control Flow "Execute DTS 2000 task" item.  
I hope this makes sense.

So even though I need only 1 Excel conneciton each Control Flow item has to 
have its own Excel connection thus forcing me to have 20 unique Excel 
connections one for each 'Execute DTS 2000 Task" item)

correct?

Thanks again

"Mark Han[MSFT]" wrote:

> Dear Customer,
> 
> In order to address your concern, i would like to explain the following
> 1 your understanding of my answer is correct.
> 
> 2 to answer you question: if creating a new Excel Data Source in a DTS 
> 2000, the data source is not be able to be shared in the SSIS package also. 
> Becuase the data source is created in the DTS 2000 package designer; and 
> every SQL 2000 package excuted by SSIS task(excute DTS 2000 package) is 
> independ on each other. For the "excute DTS 2000 package task", it uses 
> another connection, not the same as the connection in DTS 2000 package; 
> 
> If anything is unclear or you need me explain further, please tell me.
> 
> 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@microsoft.com.
> =========================================================
> This posting is provided "AS IS" with no warranties, and confers no rights.
> =========================================================
> 
>

Dear Customer,

Thank you for the update.

In order to address your concern, I would like to explain the following

The current situation
=========================
After migrating DTS 2000 package to SSIS, the task in the DTS 2000 package 
become a "Execute DTS 2000 Task"; and the connection they use  are showing 
in the  Connection Manager pane within BIDS. You would like to share these 
connection in the DTS 2000 package designer.

Explanation
=========================
1. The connection in the  Connection Manager pane within BIDS is considered 
as a SQL Server 2005 object; it is not able to be recoginized in a SQL 
Server 2000 component(DTS 2000 package designer)

2. The type of connection of SSIS task, Execute DTS 2000 Task, includes the 
following. it excludes a Excel database source.
     SQL
     Structured Storage File
     Embedded in Task

3 Each DTS 2000 Task executed by "Execute DTS 2000 Task" is indenpended on 
each other, they are not able to share connection.

Hope the above explanation can address your concern. 

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@microsoft.com.
=========================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
=========================================================

I believe I undertstand all that you are stating.

Here is what is confusing to me:
A) In the Execute DTS 2000 Package Task Editor, select Edit Package button
B) Within the package, Open(dbl-click) the Excel Source.
C) Select Existing Connection radion button.
D) The drop down list does not list all the connections that I would expect 
to be available as listed within the Connection Managers pane.  Based on your 
comments it shouldn't show any since the DTS package is independent of the 
SSIS Connection Managers.  However, It does appear to show some of the 
Connection Managers just not all of them.

For Example, if I have 5 Excel Connection Managers: CONN1 thru CONN5.

In Control Flow item 1, I may see CONN1 and CONN2 in the drop down list but 
then if I access Control Flow item 2 within the same SSIS package that is 
again another DTS 2000 Execute Task I may only see CONN4 and CONN5 in the 
drop down list.  I guess I have two questions 1) are the connections I see in 
the Connections Manager pane and in the drop down list referencing the same 
connections or are they just conicidentally the same name and 2) why does it 
appear to show some of the Connection Managers (if answer to 1 above is the 
"same connections") in the drop down list but not all.

Thus if it would show all, then I could solve my original problem and just 
have all Control Flow items use CONN1.

Thanks again for all your help.

"Mark Han[MSFT]" wrote:

> Dear Customer,
> 
> Thank you for the update.
> 
> In order to address your concern, I would like to explain the following
> 
> The current situation
> =========================
> After migrating DTS 2000 package to SSIS, the task in the DTS 2000 package 
> become a "Execute DTS 2000 Task"; and the connection they use  are showing 
> in the  Connection Manager pane within BIDS. You would like to share these 
> connection in the DTS 2000 package designer.
> 
> Explanation
> =========================
> 1. The connection in the  Connection Manager pane within BIDS is considered 
> as a SQL Server 2005 object; it is not able to be recoginized in a SQL 
> Server 2000 component(DTS 2000 package designer)
> 
> 2. The type of connection of SSIS task, Execute DTS 2000 Task, includes the 
> following. it excludes a Excel database source.
>      SQL
>      Structured Storage File
>      Embedded in Task
> 
> 3 Each DTS 2000 Task executed by "Execute DTS 2000 Task" is indenpended on 
> each other, they are not able to share connection.
> 
> Hope the above explanation can address your concern. 
> 
> 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@microsoft.com.
> =========================================================
> This posting is provided "AS IS" with no warranties, and confers no rights.
> =========================================================
> 
>

Dear Customer,

Thank you for the reply and so detail description. 

My email is v-fathan@online.microsoft.com(remove online), please send me 
some screen-shot about the issue. That will help me better understand the 
issue.

Based on your clear and detail description, I'm trying to re-produce the 
issue. However that might take me a little long time. If it is convenient 
to you, could you please provide me the DTS 2000 package/SSIS package. That 
will help us reproduce the issue quickly.

I look forward to hearing from you. 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@microsoft.com.
=========================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
=========================================================

Hi Chris,

I would like to public my explanation here:

Q: why the all Connection Manager connections within the Existing 
Connection cannot be saw in the drop-down list?
A: The existing connection in the drop-down box shows the connection 
existing in the DTS 2000 package designer. The connection existing 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. For the issue, 
Since the connection 1 copy 2 and connection 2 copy 3 exist in the DTS 2000 
package designer, they can be listed in the drop-down box; however the 
other 49 connection does not exist in the DTS 2000 package designer, so we 
are not able to find them in the drop-down box of that DTS 2000 package 
designer 

If I can assist you anything related to SQL, please post them here.

Have a nice day.

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@microsoft.com.
=========================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

Hi,

I am not sure if this is going to help you.. but you can try the
following..

Open the properites of the connection manager that you wnat to use.
Copy the guid.
Open the properties of one or more sql tasks and paste in the
connection property value.