Dynamic Execution of Child Packages with Different Parameters

SSIS 2012 introduces the concept of Parameters – a more explicit way of configuring your SSIS packages that you can use with the Project deployment model. The Execute Package Task was updated to support binding variables in the parent package to parameters in the child package (an alternative to the Parent Variable Configurations in SSIS 2005/2008).

image

Clicking the Add button on the Parameter bindings page will automatically populate the binding list with the next un-bound parameter for the child package you have selected to run. Note, however, that you can manually enter a parameter name in the Child package parameter field, even if it doesn’t actually exist on the child package.

image

At runtime, any parameters that don’t exist on the child package don’t cause any errors or warnings at runtime. There is a reason for this…

Dynamic Package Execution

You can change the which child package the Execute Package Task runs using an expression on the PackageName property.

image

Since not all packages will have the same set of parameters, the Execute Package Task allows you to define a superset of parameter bindings at design time. Parameters that don’t exist for the current child package will simply be ignored.

For example:

  • ChildPackage1 requires ParameterA and ParameterB
  • ChildPackage2 requires ParameterC
  • ParentPackage has logic that will dynamically call either ChildPackage1 or ChildPackage2 at runtime

To do this, you would add parameter bindings for all three parameters (ParameterA, ParameterB, ParameterC) in the Execute Package Task in your parent package.

image


More details and parent-child package execution design patterns can be found in Chapter 16 of the SQL Server 2012 Integration Services Design Patterns book available from Apress.

Leave a Reply

You must be logged in to post a comment.