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.
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
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:
Dim oPkg, oDataPump, sSQLStatement
'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
Let me know what you think.
Thanks in advance. Thanks so muchly !