2

I am sure this is a fairly common problem but i can't manage to find anything about it. We have an unspecified number of DB Servers from which to get data: we started with two DB servers but they can become more. All the metadata are the same across the different databases in the DB Servers. I need to load the data from all these N DBs into a single DB. So far, I have several .dtsx, each of which has two parallel flows doing exactly the same thing, one for the first DB, one for the second. I don't want to add a new flow to each of my (20+) dtsx every time we add a new DB server.

I would like to have something like a FOR loop that cycles through all my ConnectionManagers. I would create just ONE data flow and then, when a new DB server must be added, I would just add a new connection Manager and with just a little more effort everything would run again, WITHOUT the need to design an entire new data flow. Besides, If I ever need to change my dataflow, I would need to change it only once.

I found an article ("Change dynamically connections during runtime using ssis 2012") that describes how to change the connection managers. What I need now is a way to preserve PARALLELISM. I guess if I start the import from one DB server, I will have to wait until its end before the second starts. Is there a way to put my ControlFlow in a LOOP so that every cycle is started in parallel? Example: My data flow is made up of a SQLTask T1, DataFlow T2 and a inal SQLTask T3. This must work in parallel for all my dataConnections (DC1, DC2,...,DCN). I need a way to start T1 for all my dataConnections at once. I don't want T1 for DC2 to wait for the end of T3 from DC1.

I am using VS2015. SSIS is in version 12.

Thankx

[PS.: I would prefer to avoid BIML if possible]

Johannes Wentu
  • 931
  • 1
  • 14
  • 28
  • 1
    What about creating a new package with for each loop that will contain the execution of the child package? you will send the connection string as a parameter and then you can execute everything in Parallel. http://www.rafael-salas.com/2010/07/ssis-how-to-run-set-of-packages.html – LS_85 Feb 01 '17 at 12:56
  • As mentioned by rafael I would suggest to create a master package which then takes care of the parallel execution. However, since i faced a similar problem in the past, I strongly recommend to handle the parallel execution of the child packages via a C# Script task. Furthermore you should keep in mind, that you might encounter deadlocks if numerous instances of a package write into the same destination table. – Tyron78 Feb 01 '17 at 15:26

0 Answers0