How to execute sub packages in SSIS (and pass through configuration file)

Every time I need to execute a subpackage in SSIS, I am upset with the way SSIS handles the configuration between parent package and child package. Let's precise I like to use the old-fashion dtsConfig file, that allows an easy configuration for connection managers (and variables, and anything actually).

So, today I decided not to use the default "Execute Package Task" provided by SSIS.

Instead, I used a "Execute Process Task" that calls dtexec utility.

The idea is to make a generic version of this component, thanks to the powerful SSIS expressions, so that we can copy/paste the same component every-time we need to execute another subpackage.
The name of the component will be used to specify the package to be executed.

Let's do it.
First, we will prepare some variables. Create the following:

• Param_ConfigurationFile : it points to the dtsConfig file you will want to use for running your subpackages.
• Param_DtExec : the path to your dtexec utility. Generally, there is no need to give the absolute path.
• Param_PackageDirectory : the directory where the SSIS subpackages will be found.

These variables will be configured with a dtsConfig file in server environments.

Create a new "Execute Process Task".

Open its properties.
In the "General > Name" textbox, write the name of the subpackage you want to execute.
Here, the name of the subpackage will be TheSubPackageIWantToRun.

In the Expressions tab, create 2 expressions :

1. Arguments : "/FILE \"" + @[User::Param_PackageDirectory]  + "\\" + @[System::TaskName]  + ".dtsx\" /CONFIGFILE \"" + @[User::Param_PackageDirectory]  + "\\" + @[User::Param_ConfigurationFile] + " /CHECKPOINTING OFF  /REPORTING EWCDI "
• The /FILE modifier tells to DtExec which package to run (directory\package.dtsx). Note that the Task Name is used to store the package you want to run, this is really user-friendly in SSIS : at first glance, you can see in SSIS Control Flow the subpackages that will be ran.
• The /CONFIGFILE tells DtExec which configuration file will be used for subpackage (old fashion, not MSDB). In this example, I assume the configuration file is stored in the same directory that the packages. It can be changed easily.
• The last 2 options are standard and generated by DtExecUI. Not subject to this post :)
2. Executable : @[User::Param_DtExec]

So, if you want to run 3 subpackages in a row, you just have to copy/paste this generic component 2 times. In the following example, it will execute the package sub_package_1.dtsx, then sub_package_2.dtsx and finally sub_package_3.dtsx, and pass to them the same configuration file, in which you could define connection managers and variables.

Hope it may help !