December 5, 2008 at 10:30 am
I am trying to pull data from a number of database (around 320 in total) using a DTS package. I can connect to the source using ODBC as the source database is PervasiveSQL. The number of databases i need to connect to is variable so i need a way to make the update dynamic if possible.
I have created a package that has a connection to the source and destination database, the source connection i am updating using the Dynamic object and i have created a loop using activex scripts. If i step through the DTS package manually all is fine and the data loads correctly.
Eg for connection 1 i load 31 records and for connection 2 i load 636 records.
However when i run the DTS it loads data but loads the same data for each connection, connection 1 loads 31 records and connection 2 loads 31 records. When i look at the data it is exactly the same. This suggests to me that the Transform Data task is not updating to use the new source connection but is using the initial one.
Has anyone seen this behaviour before and know of a solution, or even know of a better solution for doing this task?
Regards
John
December 5, 2008 at 1:30 pm
Have you checked out the looping article on SQLDTS.com?
This usually happens from looping in the wrong place and so not updating the globalvariable properly (not that I've done it several times or anything).
December 8, 2008 at 3:13 am
Yes, i used that article myself and did reference back to it to make sure i wasnt going mad when i found my issue.
It is looping through ok and updating the ODBC connection before it runs as far as i can tell, i have passed values at each stage into a global variable and written it out to a table so i can see what occurs each step. However it never seems to load data from any other database than the initial one.
Ok i have split the package into 2 and now call the load part each time, this is working correctly.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply