April 13, 2016 at 10:25 am
I have built a package where I have multiple database connection string through a For loop Container and inside that I have several dataflow task to move the data to a central table. Now from the central table I need to move data to tables in parallel to multiple connection using the dynamic connection string .
How can I achieve this? Thanks
April 13, 2016 at 10:30 am
How are you determining the Destination servers? Are they a set group of servers, or will the destinations be changing?
April 13, 2016 at 10:40 am
John Rowan (4/13/2016)
How are you determining the Destination servers? Are they a set group of servers, or will the destinations be changing?
The destination server remains the same only the database connectionstring changes.
April 13, 2016 at 11:07 am
So then the Destination database is changing? What part of the connection string is changing?
April 13, 2016 at 11:19 am
John Rowan (4/13/2016)
So then the Destination database is changing? What part of the connection string is changing?
Data Source=serverA;Initial Catalog=database1;Provider=SQLNCLI11.1;Integrated Security=SSPI;Auto Translate=False
The "database1" changes and I need to run the different connection in parallel .
April 13, 2016 at 11:26 am
Where are the dynamic database names coming from? A table? Is it a finite list of destinations or will they need to be changed, added to?
Running the package to push to the dynamic destinations is quite simple as you know considering you've already done it dynamically for the reading of the data. The challenge will be making the destinations dynamic AND parallel.
April 13, 2016 at 12:53 pm
John Rowan (4/13/2016)
Where are the dynamic database names coming from? A table? Is it a finite list of destinations or will they need to be changed, added to?Running the package to push to the dynamic destinations is quite simple as you know considering you've already done it dynamically for the reading of the data. The challenge will be making the destinations dynamic AND parallel.
Yes, the dynamic connection string is coming from the table and the varConnString gets set in each loop iteration.
April 13, 2016 at 1:04 pm
If it's not a finite list, trying to run the load in parallel is going to be very difficult. Since you have to dynamically set the destination connection via values from your table, it has to be done in a for each container where you are looping through an ADO recordset and setting the destinations from the ado record values. By nature of the for each container, it's one at a time. Just using an ADO recordset and for-each loop, you may be able to copy your container and then just adjust each query to fill your object variable to grab only a portion of the table. Without knowing how your tables/data are set up, it's hard to tell.
There may be an out-of-the-box solution for you, but you'll need to provide more details on the process, steps, data setup. If you are willing to document the process and provide the table ddl and sample data for the table that sets your connections AND the data you need to move, we may be able to come up with something. So, table DDL for table containing connection string info, sample data, an example of the data you are moving to the destination, and a breakdown of the logical flow of your package control flow....
April 13, 2016 at 2:02 pm
John Rowan (4/13/2016)
If it's not a finite list, trying to run the load in parallel is going to be very difficult. Since you have to dynamically set the destination connection via values from your table, it has to be done in a for each container where you are looping through an ADO recordset and setting the destinations from the ado record values. By nature of the for each container, it's one at a time. Just using an ADO recordset and for-each loop, you may be able to copy your container and then just adjust each query to fill your object variable to grab only a portion of the table. Without knowing how your tables/data are set up, it's hard to tell.There may be an out-of-the-box solution for you, but you'll need to provide more details on the process, steps, data setup. If you are willing to document the process and provide the table ddl and sample data for the table that sets your connections AND the data you need to move, we may be able to come up with something. So, table DDL for table containing connection string info, sample data, an example of the data you are moving to the destination, and a breakdown of the logical flow of your package control flow....
So it's not possible to make parallel connections to the databases by reading the connections strings from a table?
April 13, 2016 at 2:32 pm
I said difficult, not impossible. This is as far as we're going to get w/o some more info from you on your process.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply