DTS - Updating DataSource connection via ActiveX script task

[Justin]

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

[Jim]

To get around this problem I had package A execute package B passing the server name as a global variable. So package B gets executed once for every server. There might be an easier way using one package, but I couldn't figure it out.


------------
Justin at 7/6/01 8:39:00 AM

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

[rajesh_2383]

Hi Justin,

I am facing the same issue as below . Can you please advice me with your experiance in this regards.

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[/Q