Changing connection datasource in loop 1

[jcaulder]

I have a DTS package created for SQL 2000 which should go out to 36 remote locations, query some data and import it into a central database.

I have this set up as a loop within the DTS package using a rowset to control the looping. The rowset contains all my remote server names and the loop keeps going until I've looped through all records.

My problem is that even though I change the connection.DataSource during the processing, the connection still always points to the location it was initialized with.

It would appear I need to force the connection closed perhaps, then set the datasource then re-open the connection.

The code I wrote in the ActiveX script looks like:

Set oPackage = DTSGlobalVariables.Parent

Set oSourceCL = oPackage.Connections("SourceCL")

oSourceCL.DataSource = sSourceDB
oSourceCL.Catalog = "Trinidad"
oSourceCL.UserID = "sa"
oSourceCL.Password = ""
'the messagebox correctly shows for all 36 remote locations
msgbox oSourceCL.DataSource

During execution, if my servers were named Server1 - Server 36, the message box would correctly pop up for every server(1 - 36). If I created the package with the connection set as Server1 however, this is the only server that is queried and it is queried 36 times. After the package completes, if I go back into the designer for the connection and look at its properties, its datasource is now set to the last server in the list(i.e. Server36).

So the datasource is changing, it is just being ignored. Is there something I need to do to force close the connection so it will reinstantiate with the new datasource.

TIA!

 

[AlexCuse]

I haven't used DTS in a while, but I think what you're looking for is the "Close Connection On Completion" setting under your Transformation task's workflow properties.

Hope this helps,

Alex

[small]----signature below----[/small]
The author of the monograph, a native of Schenectady, New York, was said by some to have had the highest I.Q. of all the war criminals who were made to face a death by hanging. So it goes.

My Crummy Web Page

 

[jcaulder]

Yep it absolutely is!! I found the answer here after an hour of searching:

http://blog.netnerds.net/2007/08/sq...ination-in-transform-data-task/#comment-10632

Works perfect now!

Thanks for the post!!

J

 

[AlexCuse]

Glad you got it working. That has burned me at least once in the past

[small]----signature below----[/small]
The author of the monograph, a native of Schenectady, New York, was said by some to have had the highest I.Q. of all the war criminals who were made to face a death by hanging. So it goes.

My Crummy Web Page