Master Child packages - Part 1: File based

Case
An often seen solution is a master package calling a couple of child packages with the Execute Package Task. This works fine for a couple of packages, but is a little boring for a whole bunch of packages. Is there an easier more clear way to maintain a master package?

Server based and file based child packages

Solution
A simple solution is to use a Foreach Loop Container with an Execute Package Task in it that loops through a folder with packages. It works both for file-based and server-based packages.

But there are a couple of drawbacks:
Drawback 1: The child packages are not executed simultaneously, but one after another. Will handle this problem in a future post.
Drawback 2: The options to determine the order of execution are limited. You can only order by name. So if a certain order is required then you need to add some prefix to the packagename to determine the order.

I have prepared three solutions:
A) File based: SSIS 2005, 2008 or 2012 if you use package deployment.
B) SQL Server based: SSIS 2005, 2008 or 2012 if you use package deployment.
C) Project Referenced: SSIS 2012 if you use project deployment. This solution is nearly equal to solution B.


A) File based
1) Variable
Add a string variable to the package and name it PackagePath. This will contain the filepath of the package.


Right click in Control Flow

2) Foreach Loop
Add a foreach loop to your master package. Edit it to give it a suitable name and to select the File Enumerator. If you need a certain order then you could install the Sorted File Enumerator.

File Enumerator

3) Path and folder
Enter the folder name where your packages are located and enter a filter (example: STG*.dtsx). Make sure the Fully qualified options is selected.

Loop through package folder

4) Variable Mappings
Go to the variable mappings pane and select the variable from step 1. This will fill the variable with the path of the current package.


Select the String variable from step 1

5) Execute Package Task
Add an Execute Package Task in the Foreach Loop. Give it a suitable name and configure it to call one of your child packages (Location = File system). Just pick one. We will overrull the path in the next step.

Calling a file based package

6) Expression
Go to the properties of your newly created File Connection Manager and add an expression on the ConnectionString property that overrules its value with the variable PackagePath from step 1.

Expression on new connection manager

7) The result
A clear package with only one Execute Package Task and one Connection Manager.

The result

Go to B) SQL Server based or C) Project Referenced