Search code examples
ssisssis-2012

How to set up SSIS parent package such that 4 child packages can run at the same time with different parameter values passed in?

I have created a child SSIS package that executes according to the "ProcessName" variable value that is specified initially. Now, I wish to create a parent package such that I can execute 4 child package tasks with different ProcessName values passed in to be executed in parallel. How can I maintain my child package and pass in different values to each of the 4 execute packages task such that the ProcessNames variable values are different for each of them? I am new to SSIS and would deeply appreciate if someone could advice or give a direction on how I could go about doing so.

Solution

  • I would see this as a pattern like the following

    enter image description here

    The "trick" here is that within each Sequence Container, SEQC, I need to define my variable that holds my parameter value. That variable needs to be scoped to the container - otherwise, there is only one SSIS variable and the 4 processes that attempt to initialize that value will be in conflict.

    In the SSIS Variables menu, there is a Move Variable icon (second one listed)

    enter image description here

    Here you can see that I have ParameterValue defined in both "SEQC Opt 1a" and "SEQC Opt 1b" and they're initialized with different values.

    The first step within the Sequence container is an Execute SQL Task where I pull back the intended parameter value. Maybe that is not needed in your case but it can be helpful to have a repository of run-time values. In the case of 1b, this is much more what my execution pattern looks like. I have a query that pulls back any packages to be run within the scope of this container and the starting value. e.g.

    ContainerName|PackageName|StartingValue SEQC Opt 1a |Child0.dtsx|100 SEQC Opt 1a |Child1.dtsx|200 SEQC Opt 1a |Child2.dtsx|300 SEQC Opt 1b |Child5.dtsx|600 SEQC Opt 1b |Child6.dtsx|700 SEQC Opt 1b |Child7.dtsx|800

    This table pattern allows me to dynamically run packages in both parallel and in serial. Assuming Child7 and Child2 in the above set are very slow but the other 4 packages are relatively fast. The fast ones would start up, do their work and complete and the next runs. There are limits to how many parallel operations can fire at once so you can't scale infinitely across processes so a balance of serial and parallel operations makes sense.

    Once you have your pattern working for one sequence container: copy, paste, rename and assuming you lookup in a table based on the task name as I show above, it's ready to go.