To start things off, we need to start with our parent package. A parent package is merely just a package that calls other packages. In this example we’re going to look at the cleverly named parent package called ‘Parent Package.dtsx’.

In this package we have a For Each Loop container that is going to look at a specific directory and look for all files within that directory that end with .tbl . The .tbl files are synthetic data files generated by a tool called DBGen Synthetic Data Generator. You can find the downloadable executables as well as the necessary documentation here.

Please don’t get alarmed by seeing the child package’s contents. It looks a little crazy but today’s lesson, combined with Thursday’s class, will clarify things a bit. Today we’re going to focus on how the parent-child relationship is formed and how the child package utilizes the values of a variable from the parent package. In our child package, what we have are individual Sequence Containers. Each container corresponds to an individual table that is to be loaded using our synthetic data. What I’ve done is I’ve setup a precedence constraint on each container to only execute when the corresponding value from the parent package is passed through. Not familiar with precedence constraints? Don’t worry, I’ll be covering those on Thursday! In this example, if the value of the variable in the parent package is ‘orders’, we will only execute the Sequence Container for Orders.

The second is to setup a special type of package configuration in order to tie the two packages together.

To do this, right-click on the Control Flow of the child package and select Package Configurations from the submenu. You will need to check off the box for package configurations, as they are not enabled by default. Create a new configuration with the type of Parent Package Variable. In the box for Parent Package variable you are going to specify the name (case-sensitive) of the variable being passed from the parent package. In our case, we want the variable called TblName (again note this is the name of the variable located in the parent package). When you click Next, you’re now going to map that variable being passed to a variable in the child package. In the objects explorer navigate to your Variables folder and select the Value property for the variable of ‘Parent_TblName’. Click Next, give your configuration a name and click Finish and then Close. So again to clarify, what we’ve done here is that we’re passing the value of ‘TblName’ from the parent package, the value of ‘Parent_TblName’ in the child package. Congratuations! You’ve just setup your parent-child package!

Now, when we run this package, for each item name the For Each loop cursors over (i.e. orders.tbl, customers.tbl, region.tbl) your parent package will call the child package each time. Each time it calls the child package it will pass the name value through to the child package. This variable value will tell the child package which specific Sequence Container to execute on that pass. Once that Sequence Container’s work is done, the child package tells the parent it finished and the For Each loop will iterate through until it is done executing all of the items in the array. In Thursday’s class we’ll delve more into the precedence constraints and how they are working here and how they’re also utilizing the variables being passed from the parent.