Hi,

I've written a DTS Package to pull information from a number of servers. I have a list of servers (Source Servers) and for each one I automatically update the Datasource property of the Source Server connection using vbscript in an ActiveX Script task and a global variable.

I have a transformation that occurs between the Source server and Target server.

As I process each Source Server in turn, the global variable gets set correctly and so does the Datasource property of the Source Server connection. However the transformation still runs against the first Source Server that was processed.

Looking at BOL it appears that a transformation stays connected even after it has finished. Therefore I assume that although I change the Datasource of the connection to a new server, it still runs against the first server that was used.

My question is how do I get the connection to run against the updated Datasource, i.e. do I need to do a refresh of the Connection, disconnect or what ?

Any ideas would be much appreciated.

thanks