Trying to connect to different Interbase databases via Interbase ODBC driver.
-After connection, use a datapump to select data from db A
-Then loop, based on a global variable that holds the count/ID of the new database B to connect to.
-GLobal variable successfully getting updated with new branch details
-ODBC driver 'seems' to be switching to new branch db B
-But datapump goes to database A and tries to select the same data again it has done already.
-Loop fails. But if all steps run manually one by one the loop works i.e goes to A then goes to B.
I had a similar situation. I had a list of databases on remote servers that I had to connect to and upload 10 tables from each database. I would dynamically change the database connections, but the DataPump tasks seemed to hang on to the previous connection. I solved it by doing this.
Put the two database connections and all the dataPump tasks in a seperate DTS package.(child package) The Parent package will handle the looping though the list of databases, and pass global variables from the parent to the child package(db name, db location, db password, etc).
In the child package, have an ActiveX script that is the first thing to run. This script is where you set the source database connection to the value of the global variable that was passed in.
Also, I found out that there is a property on the source connection (ConnectImmediate) that needs to be set to True.
Set oPkg = DTSGlobalVAriables.Parent
set oConn = oPkg.Connections(source DB)
set oProp = oConn.Properties("ConnectImmediate")
oProp = True
Look at the article in SQLDTS.com regarding the looping in DTS. It will fill in the blanks.
The parent calls the executepackage task and the child package connects to the database and pulls in the tables. When the child task completes, the control goes back to the parent, and it loops through to connect to another database.