Pass Parent Package Values to Child Packages via Parameter Binding

Introduction

I was working on a project that required passing of variable values obtained from a parent package query to the child packages and want to share with you how I was able to achieve this. For my project, I had to get the latest date from the Insert Date column and calculate the number of days lapsed from that value to the present date. That calculated value was set as a variable in the parent package. I searched for how to pass variable values between parent and child packages and found a process of utilizing a script task to set the value. I tried this method and it worked for me when I ran the parent package locally, but when I deployed to the SSISDB and run from a SQL Agent job, I was not getting the same result.

I then stumbled upon a mixture of using parameter binding and setting up child package parameters that could be set and utilized at runtime. I ran tests and it worked both locally and when deployed to SSISDB. Here is how I carried out the implementation. We are going to carry out 7 simple steps to get you there!

Here is what my solution looks like with a parent and child package.

How To

Step 1: Set up a variable in the parent package. For this example, we will create a ‘DaysToLoad’ variable that is an integer and leave it set to 0.

Step 2: Set up SQL Task with query to calculate your days to load and set the ‘DaysToLoad’ variable you created. (Make sure to set your Result Set setting to ‘Single Row’)

Step 3: Use Result Set sub Menu in your SQL Task to set the value of the DaysToLoad variable based on the result of your SQL query you entered in the prior step.

Step 4: Within your child package you want to go to package parameters in your package ribbon and create a parameter to be set by your parent package variable.

Step 5: Once this parameter is created, you will be able to use it in Parameter settings like I did here in an OLEDB Source module.

Step 6: Go back to your parent package and set up your execute package task with the parameter binding to your child package parameter you created in Step 4.