Changing connection datasource in loop
Changing connection datasource in loop
(OP)
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:
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!
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:
CODE
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
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
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!
Talk To Other Members
RE: Changing connection datasource in loop
Hope this helps,
Alex
----signature below----
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
RE: Changing connection datasource in loop
http://blo
Works perfect now!
Thanks for the post!!
J
RE: Changing connection datasource in loop
----signature below----
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