SSIS parallel execution

I have a package which has a sql task,gets the server and DB information from a table and stored in a
variable.The sql task is connected to a for each loop container which has data flow task,the data flow task
uses the server and data base from for each enumerator for its connection string and executes for the first set,
suppose if i have 3 servers and 3 DBs,the data flow task executes sequentially for each server.Howdo i make
this operation to run parallely for each server and dynamically picking the values of server and DB info from tables.

Thanks
nani22Asked:
Who is Participating?
 
Megan BrooksSQL Server ConsultantCommented:
Is there a limit to the number of servers, such that you could create the data flow task with at least that many sources/transformation/destination sequences? You would also need at least that many connection managers (or pairs) to do it that way.
You could initialize the connection managers to point to an empty dummy source table and empty dummy destination tables, to allow the package to validate and to avoid runtime errors.
The inital query and loop would populate the connection managers with the right connection information, for as many server/db rows as you actually had. The rest of the connection managers would still point to the dummy tables and, in effect, wouldn't be used.
If you have a large number of server/db pairs, you could do something similar but just process, say, 5 at a time. This would be complicated, of course, because the loop would need to read n at a time records and any left over connection managers on the last pass would have to be pointed back to the dummy tables.
0
 
nani22Author Commented:
i have 10 servers and 10 databases,1 DB for 1 server.do i need to have 10 data flow tasks and 10 connection managers.would there be any way to run the packages parallely by picking up the configuration dynamically.
0
 
carsRSTCommented:
1.  Add 3 variables
2.  Add 3 connection managers
3.  Use a SQL Task to populate each variable with a connection string
4.  For each connection, find "expressions" and populate the connectionstring with your variables.  Use format below...
     @[User::connectionstring1]  <--one of your 3 variables

0
 
Megan BrooksSQL Server ConsultantCommented:
Probably 11 connection managers, assuming that the destination connection is fixed, but only one data flow task. That is how you make it run in parallel -- all of the individual flows defined in parallel in data flow task.
What happens when the package runs is determined by the state of the connection managers when the data flow task starts. You could set that state from a ForEach loop container or you could set it from a package configuration file and yes, the configuration file could be generated dynamically, but it would have to exist when the package started.
I think it would be easier to set the connection managers in a ForEach loop. 10 parallel flows within a data flow task should not be a problem. I believe I have gone much higher. You could include a few more for future expansion if needed.
All that is really required is to set the connection string for each connection manager with a statement of the form
Dts.Connections["connectionmanagername"].ConnectionString = "connectionstring";
Of course "connectionmanagername" and "connectionstring" would more likely be variables than constants. I am going by memory at the moment and the above statement may not be exactly right but it is close. I think it would be easier to do this in a loop than to create a configuration file. The connection manager names would end in numbers, making it easy to assign them.
0
 
nani22Author Commented:
gfgfrt
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.