Results 1 to 2 of 2

Thread: DTS Looping

  1. #1
    Join Date
    Oct 2003

    Red face DTS Looping

    I have read articles on DTS looping,103,246,0,1 and a few others, but I still have a problem.

    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.

    HeeeEEEllLLLppppp !

  2. #2
    Join Date
    Feb 2004

    DTS + Looping

    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.

    dim oPkg
    dim oConn
    dim oProp

    Set oPkg = DTSGlobalVAriables.Parent
    set oConn = oPkg.Connections(source DB)
    set oProp = oConn.Properties("ConnectImmediate")
    oProp = True

    Look at the article in 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.

    Hope this helps.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts