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.DataSourceSo 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!
