Welcome to the second week of SSIS this semester at SQL University. Today we’re going to talk about the relationship between children and parents. Ever had communication issues with your kids when you ask them to complete a chore? When they’re done, wouldn’t it be nice if they always came back and let you know they took what you said, applied it, and completed the job? What does that have to do with SSIS? Read on and find out!
As parents sometimes you have a large task to accomplish and you need the whole family to pitch in and help out. In SSIS we can accomplish something similar by setting up packages in a configuration we call (wait for it) Parent-child packages. Original name, eh? In today’s example we’re going to look at a parent-child package setup I’ve created that loads generated data into their corresponding tables.
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.
As the task executes and looks through the designated target folder, it will take the name portion of the file without the extension and populate a variable called TblName with that value (i.e. The value for orders.tbl is simply ‘orders’). This step is important in this case as we will be utilizing this variable in our child package to perform our processing.
Once the variable is populated we’re going to execute our child package called, originally enough, Child Package!
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.
So how does the child package get to see that variable’s value anyways? I’m glad you asked! In order for the child package to be able to “see” this value getting passed, we have to do two things: The first is to create a variable in this package that will hold the value passed from the parent. For this example I’ve created a variable in the child package called ‘Parent_TblName’. Note that I used the same name only I preceeded it with ‘Parent_’. The name of the variable does not have to match in order for this to function, however, using this naming convention helps you quickly identify the fact that this variable is being used to hold the value from another package. Also, while the name does not have to match that of the variable in the parent package the data type does. Make sure both variables have the same variable type.
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.
If you’d like to download this package to study the components, you can download the project solution files here
4 replies on “SQL University: Parents Just Don’t Understand”
[…] back, class! In our last class, we setup a parent-child package configuration and showed how you can pass variables between the […]
[…] back, class! In our last class, we setup a parent-child package configuration and showed how you can pass variables between the […]
Seems like this passing variables to child packages does not work anymore. Everyone keeps giving the same steps but I’ve already come across dozens of posts saying it doesn’t work. Clearly there is something more that we need to do. Have had 5 people working on this for 3 hours now and none of us have been able to get it to work.
Check the scope of your variables. Make sure both scopes are set at package level, otherwise I don’t believe it will work.