Loop to change ODBC DSN Problem
-
I have a loop that is meant to transform data from multiple ODBC sources into a single SQL database/table ... but it is not working. Without the loop the transform works fine for one ODBC source.
I have eight proprietary databases that have exactly the same database name, table names and table structure. The eight databases represent different companies. These eight company databases are located in different network folders. There is a separate ODBC System Data Source Name for each company database. I want to transform the contents of one table from each of company database into a single SQL database/table. The one table from each of the eight company databases has the exact same name, same field names, same field types etc (and as previously stated the database name is exactly the same in all eight cases).
Within a loop I’m populating a Global Variable which stores the ODBC Data Source Name of the company database that is about to be transformed. Within the loop I’m also using a Dynamic Properties Task to set the DataSource Property value of the ODBC Connection to this Global Variable.
After executing the loop the values in the SQL table that have been inserted are the values from just one of the company databases repeated eight times. It is the company for whom the initial Data Source Name was entered when the DTS ODBC Connection was first inserted into the DTS Package.
At first I thought the Global Variable setting and/or loop and/or DataSource Property reset was not working. However, when I immediately view the DTS ODBC Connection it displays the eighth company’s Data Source Name … which tells me the Global Variable setting and loop and DataSource Property reset are all working. Further if I then execute just the Data Transform Step, the values of the eighth company are updated into the SQL table.
For what it’s worth the underlying database/table structure is a proprietary one from the application MYOB.
Having said that I’m getting the same symptoms when I replicated the above but changed the ODBC DSN to point to SQL databases.
What am I doing wrong?
Regards
BanOcto
-
DTS doesn't have a looping mechanism, not directly. So, how are you doing the loop? Please explain how you doing that, and I will be able to help you a little better. If possible, can you do a screenshot of the package open in DTS Designer and post the picture on here.
My blog: SQL Soldier[/url]
SQL Server Best Practices: SQL Server Best Practices
Twitter: @SQLSoldier
My book: Pro SQL Server 2008 Mirroring[/url]
Microsoft Certified Master: SQL Server, Data Platform MVP
Database Engineer at BlueMountain Capital Management[/url] -
Sounds like while you are changing the connection string to point to the new server, you are not (re)establishing the connection to the new server afterwards....
I'd agree...please post a sample of the code being used.
-
Thanks Robert & Andrew for contributing to this thread.
We’ve found a rather nasty way to get around this problem.
I’ll make another attempt at a decent solution in the future.
Cheers
B
-
If it's not too much trouble (and not tooooooo nasty
) could you please post your workaround in case others have the same issue in future? -
Ian,
I appreciate the need to share solutions ... but our solution is tooooo nasty. We have implemented a high maintenance solution which is not scaleable … but we will revisit!
Cheers
-
Thanks everyone for your input. We’ve come up with a reasonable solution. It’s not best practice … but it works and it’s not too nasty. In short we have an outer package that does the looping. One of the steps in this loop executes another package (i.e. inner package) and passes the relevant variables. This inner package has no looping and successfully inserts the correct data and overcomes the initial problem I reported in this thread.
Cheers
b
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply