I am trying to write a DTS package at work that uses a loop to fire a query off against a different server/database at each loop iteration. Prior to the execute SQL task , I use ActiveX (VBScript) to change the Catalog and DataSource of the connection (created by drag and drop if icon). My Execute SQL task selects @@servername and getdate() just as a dummy query to make sure I am actually pointed at a different server.
While testing the properties of the connection using global variables and msgbox shows that the database (catalog) and server (data source) ip address is being changed, the result returned is always from the first server. I feel like I need to disconnect the connection object, change the parameters and then reconnect to the new server but there doesn't appear to be anyway to do this.
Anyone out there able to successfully change a connection object inthis manner??
I figured it out myself but just in case anyone else has this problem:
In order to use activeX to change a connection object DataSource/Catalog (server/database) from one SQL server to another one (different physical box) via ActiveX Script task you must set the RESUABLE property to 0 rather than the value -1 as mine had defaulted to. Now it all works like a charm....