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 !See Attached file for further details|||Hello,
Hoping somebody would have an idea on this one...
Please Help.|||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|||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/features/mssql/article.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|||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/features/mssql/article.php/1461481
Maybe I have luck here.

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

Thanks again.|||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|||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