Loop to change ODBC DSN Problem

[TeamGD]

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?

Cheers

 

[Catadmin]

At a guess, your loop is populating the connection information all at once. It loops 8 times, overwriting the previous connection info, then when it hits the end, the DTS package goes out, grabs the data and does the processing 8 times.



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[AlexCuse]

Sounds to me like that is what's happening too.

Have a look at this link:

http://www.sqldts.com/default.aspx?6,103,246,0,1

And post back whether or not your loop is set up in this fashion or not (using the ability to set step execution status to waiting, etc...)

Hope it helps,

Alex

A wise man once said
"The only thing normal about database guys is their tables".

 

[TeamGD]

Thanks CatAdmin & AlexCuse.

CatAdmin, I agree that what you’re saying certainly appears to be what is occurring … but I do not have the experience to resolve this. I’ve mucked around with including Begin Transaction & Commit in a SQL Task within the loop … but with no success. Do you have any suggestions on how to process the transactions within each loop, not at the end?

AlexCuse … I do use a similar technique as discussed at

http://www.sqldts.com/default.aspx?6,103,246,0,1

. The main difference is that I avoid ActiveX Scripts where possible. For example I use a Dynamic Properties Task to set DataSource Property at the start of each loop, and I use a Dynamic Properties Task to set the ExecutionStatus Property at the end of each loop.

I’ve used the exact technique (as the one I’m using now) to successfully loop through text files and process transactions to a SQL table within a loop. But my current requirement involves looping through multiple ODBC Data Source Names (DSN).

I’d appreciate any suggestions for a solution or troubleshooting steps.

Cheers

 

[AlexCuse]

Without really seeing your package and being able to dig into it, it is hard for me to think of much.

One thing I would try first, is to add an activeX task that simply prints your data source name each time through the loop. I would extract this info from your actual data source, rather than the global variable (or perhaps display both) to make sure that your data source assignation is working correctly.

Let us know what you find out.

ALex

A wise man once said
"The only thing normal about database guys is their tables".

 

[Catadmin]

Question. Are you doing your looping with a container or via an ActiveX or T-SQL task?



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[TeamGD]

Catadmin,

I use a SQL Task with Global Variables to determine a loop counter. I use a Dynamic Properties Task to set DataSource Property at the start of each loop, and I use a Dynamic Properties Task to set the ExecutionStatus Property at the end of each loop.

cheers

gmhm

 

[AlexCuse]

Man, this is a time when it would really be helpful to be able to post screenshots. Would you mind mapping out all of your steps? (the best you can with words...)

I am wondering if the position of your SQL task in relation to the loop could be causing problems?

A wise man once said
"The only thing normal about database guys is their tables".

 

[TeamGD]

Thanks everyone for your input.

We’ve come up with a reasonable solution. It’s not best practice … but it works. 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

g