SSIS Parent-Child Architecture in Package Deployment Mode

In my first post in this series, I covered the essentials of SSIS parent-child design patterns, including discussing a number of advantages for using such patterns.  In this post, I’m going to demonstrate how to build parent-child structures using package deployment mode.

SSIS Parent-Child Architecture in Package Deployment Mode

Package deployment mode is available (though not the default behavior) in SSIS 2012 and 2014, and is the only deployment mode available in SSIS 2008 and earlier.  You might wonder why I might cover this deployment mode, since it’s no longer the preferred method for SSIS 2012 and 2014, and older versions that require package deployment mode are at least six years and two versions old.  I share this information for two main reasons.  First of all, even though package deployment mode is less commonly used than project deployment mode, there are many organizations still using it – even in recent versions of SSIS – due to legacy packages that were imported from older versions.  Additionally, it seems everywhere I go, folks are still running mission-critical packages on older versions of SSIS (especially 2008), and when I demonstrate features in SSIS 2012 or 2014, I’m frequently asked about the equivalent behavior in those older versions.

Under the package deployment model, there are generally three ways to execute one package from another package:

• Execute Package Task – This is the built-in tool in SSIS that allows the developer to execute a package from another package
• dtexec.exe – This is the command-line tool for executing packages
• dtexecui.exe – This is the user-friendly version of dtexec, with a full UI for executing packages

Each of these three methods can be used to execute packages stored on either the file system or in MSDB.  Because this post addresses parent-child structures, which typically involves packages executing other packages, I’ll focus on the execute package task.

Execute Package Task

Using the execute package task in package deployment mode is a relatively straightforward process; simply drag the execute package task onto the control flow surface and set the configuration options.  As shown in the screenshot below, executing a package stored in MSDB requires only a connection to the SQL Server instance and the full path of the package to be executed.  Optionally, you can also specify the password (if the child package to be executed is password protected) and choose to execute the package in a separate process.

Similar options are available for a package deployed to the file system.  Note that you must create a file connection to connect to the package stored in the file system as shown below.

Under the Expressions tab (shown below), you can optionally configure dynamic values using SSIS expression language.  For example, you could add some logic that would dynamically specify the name of the package, which can be useful when building a work pile pattern (which I will cover in a future post).

Passing Values

So far, all straightforward, with very little to configure, right?  For a simple parent-child setup, this may be all you need to do.  But what if you want to make this arrangement more flexible – for example, you need to add some values to pass from the parent package to the child package.  A quick review of the execute process task reveals exactly zero hints on how to accomplish this.  By design, the parent package does not explicitly pass values down to child packages; rather, the child package declares – through package configurations – which variables it will consume from the parent.  So, for sharing variables from parent to child, the task list will be:

• In the parent package, create and populate the variables to be shared with the child package
• In the child package, create variables that will be populated by values from the parent
• In the child package, create package configurations – one per variable – to populate the child variables from those in the parent

Creating the variables is very simple.  Start with the parent package, creating a couple of variables – named vIntVar and vStringVar – and setting their values, as shown below.

Next, in the child package (shown below), create two corresponding parameters which will be used to store the variable values from the parent package.  Note that in this example I have not assigned a default value to the String parameter, since the parent package will be supplying that value at runtime.  For this example, you’d still have to supply a default value for the Integer (Int32) parameter, because this data type requires a non-NULL default value.

That’s the easy part.  The more obscure and difficult part is adding in the configurations for these variables. When working in package deployment mode in SSIS 2014 or 2012 (or on any package on SSIS 2008 or 2005), package configurations are used to allow child packages to consume parameter values from the parent package.  The package configurations, defined on the child packages, specify which values are to be received from the parent package, and which variables those values should be written into.   To create the package configuration in the child package, right click on an empty space in the control flow and select Package Configurations….  You’ll need to click the checkbox to Enable package configurations, if you haven’t previously used configurations in this package.  The empty Package Configurations Organizer window is shown below.

To create a new package configuration, click the Add… button to open the Package Configuration Wizard window.  On the Configuration type setting, choose Parent package variable, and type in the name of the first variable to configure (vIntVar) as shown below.

Wait – I have to type in that variable name?  Why not have a drop-down list to let me choose it?  That’s an excellent question, and one that I too asked when I first started with package configurations.  The reason you have to type in the variable name is that the child package has no explicit relationship to the parent package.  Theoretically, this child package could be executed from any parent package – it’s not bound to any particular parent.

Once you’ve typed in the name of the parent package variable, click Next > and go to the next configuration page.  Here, you specify where to put this value from the parent package variable.  As shown below, you will use the package tree view to specify the variable to which you’re going to write this value.  Drill into the variable name, expand the Properties beneath that variable, and click on Value to select it as the target of this configuration assignment.

Just as a side note, if you drill into the folders below the list of variables, you can see that you don’t necessarily have to use an SSIS variable in the child package to capture the value from the parent package; you can write directly to other properties of the package (such as connection strings).  For a variety of reasons – most notably, easier troubleshooting – I recommend that you always write a value from a parent package configuration into an SSIS variable in your child package.  You can always assign that child package variable value to a built-in value – for example, a connection string – using an expression elsewhere in the package.

After selecting Variables –> vIntVar –> Properties –> Value, click Next > and supply a name for this configuration as shown below.  To keep things simple for this example, I’ve simply named the configuration with the same name as the variable (though you can give it a different name, if you prefer).

For the other variable (vStringVar), you can repeat this process, mapping the variable of that name in the parent to the variable of the same name in the child package.

With both configurations in place, the package is ready to be run for testing.  On the child package, I’ve created a script task that will open a Windows message box to show the variable values to confirm that they are coming from the parent package.  As shown below, the message box window from the script task in the child package confirms that the child package parameter values are supplied by the parent package.

As I discussed in my previous post, you now have a simple package infrastructure that lends it self to easier development and troubleshooting, streamlined error handling, and less code repetition.  It’s difficult to see with just two packages, but with an ETL system with dozens or hundreds of packages, the advantages of these parent-child patterns quickly become obvious.

Parting Thoughts

A few things to keep in mind when using the execute package task in package deployment mode:

• The parent package variable and child package variables do not have to have the same names.  I kept the same names in this example for clarity, but there’s no technical requirement that you name those the same.  The binding between parent package variable and child package variable is established in the configuration, not by virtue of naming.
• If you create a parent package variable configuration for a variable that does not exist in the parent package, the package execution will not fail for that reason; it will simply leave intact the default value (if any) for that child package variable.  Other failures might occur related to that missing parent package variable (for example, a missing connection string if you are using configurations to pass connection strings), but a missing parent package variable alone won’t cause a package failure.
• The transfer of values from parent to child is a one-way transfer.  The information is passed by value, meaning that a copy of the value (not a reference to the original value) is given from parent to child.  Therefore, if you modify the value of the child package variable that was originally loaded from a parent package configuration, it has no impact on the original value in the parent package.  I will demonstrate in a later post in this series how to pass values by reference from the child package back to the parent package.

Conclusion

In this post, I’ve briefly demonstrated a simple pattern for implementing a parent-child architecture using the package deployment mode.  In my next post, I’ll go into depth on using this architecture in the project deployment mode in SSIS 2012 and 2014.