Results 1 to 8 of 8

Thread: DTS Looping

  1. #1
    Join Date
    Oct 2003
    Location
    Australia
    Posts
    15

    Unhappy Unanswered: DTS Looping

    DTS Looping
    Hi,
    I have read articles on DTS looping http://www.sqldts.com/default.aspx?6,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.

    Problem
    -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 !
    Last edited by bhandp; 10-07-03 at 04:06.

  2. #2
    Join Date
    Oct 2003
    Location
    Australia
    Posts
    15
    See Attached file for further details
    Attached Files Attached Files

  3. #3
    Join Date
    Oct 2003
    Location
    Australia
    Posts
    15

    DTS Looping

    Hello,
    Hoping somebody would have an idea on this one....
    Please Help.

  4. #4
    Join Date
    May 2003
    Location
    UK
    Posts
    220
    Howdy

    Not being a DTS coding expert, I'd suggest adding an extra step in the branch choosing VB script.

    If its a timing issue, this will allow the correct value to appear where its supposed to be , ready for the next sweep of the reading program.


    Cheers,

    SG

  5. #5
    Join Date
    Oct 2003
    Location
    Australia
    Posts
    15

    Arrow

    Thanks SG.
    Never thought that it could be a timing issue. Will into this.
    But found another article on dynamically changing connections in a DTS....
    http://www.databasejournal.com/featu...le.php/1461481
    Maybe I have luck here.

    Question:
    Can one call a DTS Package from another DTS Package ?

    Thanks again.


    Originally posted by sqlguy7777
    Howdy

    Not being a DTS coding expert, I'd suggest adding an extra step in the branch choosing VB script.

    If its a timing issue, this will allow the correct value to appear where its supposed to be , ready for the next sweep of the reading program.


    Cheers,

    SG

  6. #6
    Join Date
    Oct 2003
    Location
    Australia
    Posts
    15
    Still Trying..........SO GO ON Sql Experts.take a chance with this one !

    Originally posted by bhandp
    Thanks SG.
    Never thought that it could be a timing issue. Will into this.
    But found another article on dynamically changing connections in a DTS....
    http://www.databasejournal.com/featu...le.php/1461481
    Maybe I have luck here.

    Question:
    Can one call a DTS Package from another DTS Package ?

    Thanks again.

  7. #7
    Join Date
    Oct 2003
    Posts
    3

    DTS Looping

    Originally posted by bhandp
    Still Trying..........SO GO ON Sql Experts.take a chance with this one !
    Are you resetting any database properties within the datapump task? This one quite often trips people up as DTS uses the fully qualified name of the table eg: database.owner.table

  8. #8
    Join Date
    Oct 2003
    Location
    Australia
    Posts
    15

    Re: DTS Looping

    Originally posted by phillcart
    Are you resetting any database properties within the datapump task? This one quite often trips people up as DTS uses the fully qualified name of the table eg: database.owner.table
    Hi Phill,
    How can I give a fully qualified name of a table 'database.owner.table' within the datapump task when my database will change with every time it loops ?
    Is there a possibility that, the first database the datapump connects to, remains stored in the datapump ? Here is the script the datapump uses, in its properties:

    Function Main()
    Dim oPkg, oDataPump, sSQLStatement
    Dim FromDt
    Dim ToDtLocal

    'Assign global variable to local variable
    FromDt = DTSGlobalVariables("FromDate").Value
    ToDtLocal = DTSGlobalVariables("ToDtLocal").Value

    ' Build new SQL Statement

    sSQLStatement = "SELECT all the fields here"
    Where d.updDT >= '"& FromDt &"' " & _
    "AND d.updDT < '"& ToDtLocal & "' "

    ' Get reference to the DataPump Task
    Set oPkg = DTSGlobalVariables.Parent
    Set oDataPump = oPkg.Tasks("DTSTask_DTSDataPumpTask_1").CustomTask

    ' Assign SQL Statement to Source of DataPump
    oDataPump.SourceSQLStatement = sSQLStatement

    ' Clean Up
    Set oDataPump = Nothing
    Set oPkg = Nothing

    Main = DTSStepScriptResult_ExecuteTask
    End Function


    Let me know what you think.
    Thanks in advance. Thanks so muchly !
    -Parul

Posting Permissions

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