WilberBeast

I recently found myself having to replicate the same piece of script code in a SSIS package multiple times, which of course makes any developer feel dirty as copied code tends to drift divergently and not be maintained correctly, especially where the number of copies is beyond a manageable few.

Given restrictions of various clients that I’ve worked with I’ve had to avoid creating SSIS extensions or DLLs that would have to be deployed along with the package. This has meant that code reuse is always a little tricky. The tricky aspect is that in order to reuse a script in any meaningful way parameter values need to be passed in and return values received back. Thus, with plain SSIS this is difficult.

Or so I thought. I’ve now found that it is possible, and in fact easy to pass parameters down to child packages, via the well-known ‘Parent package variable’ configuration, but also to pass values back up to the parent package. Below I’ll walk through a simple example showing how this is done.

Firstly, create a parent package as shown below. We’ll fill in the details shortly.

The parent package will execute a child package two times in parallel to show that there are no issues with parallel running.

The Sequence Container labelled ‘Start’ is purely a placeholder and contains no sub-tasks.

The two parallel Sequence Containers each contain a call to the child package and a script task to message box out the result. Message boxes clearly are not the best thing in an SSIS package, but they suit our purposes for this demo.

Against each Sequence Container I set up two variables, one as a parameter value to pass to the child package and another to store the result coming back. To show that the parameter value passed down is consumed correctly each container is given a different value as shown in the screenshots below.

The code to show the message box containing the resultant value is shown below. Don’t forget to add ‘User::Result’ as a read only variable for the script task.

public void Main()
{
    // display result in a message box
    MessageBox.Show(Dts.Variables["User::Result"].Value.ToString());

    // return success as the task result
    Dts.TaskResult = (int)ScriptResults.Success;
}

Before we can set up the task to execute the child package we first need to create the child package.

The child package simply consists of two script tasks. The first to create a resultant value from the parent parameter value and the second to sleep for 5 seconds to show parallel running.

In order to access the parent parameter value we need to set up package configuration on the child package. To do this, firstly create a variable on the child package to hold the parent value.

Secondly, right click anywhere on the background of the child package and select ‘Package Configurations…’.

Tick the check box labelled ‘Enable package configurations’.

Click ‘Add…’ to add a new configuration item which will pop up the Package Configuration Wizard as per the screenshot below.

Select ‘Parent package variable’ as the configuration type.

For ‘Parent variable’ simply enter the name and not the full path of the parent variable, ‘ParameterValue’. By not entering the path, the variable is searched for initially in the scope of the task calling the child package and if not found, then upwards from there. Since we want to reuse the child task it is important that a full path is not specified so that we can pick up the value from wherever the child task is called from.

Click ‘Next >’.

Now we need to assign the parent value to a property within the child package. Select the ‘Value’ property of the variable we created earlier called ‘ParentParameterValue’ as per the screenshot below.

Click ‘Next >’.

Finally, give the configuration a name as per the screenshot below. Again, I’ve chosen ‘ParentParameterValue’.

Finally, click ‘Finish’ and then ‘Close’.

So now, when the parent package is executed, the variable values set in the parent package can be picked up in the child package. Let’s now see how we can pass result values back. It is surprisingly simple.

Edit the script task in the child package called ‘Set Result Value’. In the initial dialogue box we need to set up the variables that the script can access. Set them up as per the screenshot below, i.e. adding ‘User::ParentParameterValue’ as a read only variable and ‘User::Result’ as a read/write variable.

But wait! What is this ‘User::Result’ variable. We’ve not set it up in the child package. The script task won’t complain. What this in fact will refer to, once the package is run, is the nearest variable of that name in scope, starting from the child package, but then bubbling up the search through to the parent package starting at the point where the child package is called and then onwards and upwards. One way to think about it is to imagine that a copy of the contents of the child package is in fact inserted into the parent package in place of the ‘Execute Child Package’ task.

Once you think of it like that, the next logical question is whether it is possible to read parent package variables without having to add in the ‘Parent Package Variable’ configuration, and it is. So long as no explicit paths are given a variable reference will bubble up from the scope of the child package through to the parent calling package just as if the child were in fact part of the parent.

So why do we need ‘Parent Package Variable’ configuration at all? It is needed because validation of the child package for nearly all task types will fail if the variable references don’t point to variables that the validation process can see, i.e. are in scope in the current package as packages are validated in their own right and not in the context of any calling package. For Script Tasks this variable validation is not carried out. Variables within the script itself are available through the Dts.Variables object which is an aggregation of all variables in scope at the time of execution going up from the child package up to and including the parent package. Dts.Variables also has a ‘Contains’ method to allow you to check for the existence of a variable before use.

So, back to the demo. The script itself is shown below and simply shows that the parent value is accessible and also adds in a message from the child.

public void Main()
{
    // set the result value to be the parent value plus some changes
    Dts.Variables["User::Result"].Value = "Result: '" + Dts.Variables["User::ParentParameterValue"].Value + "'. Child says hello.";

    // return success as the task result
    Dts.TaskResult = (int)ScriptResults.Success;
}

The script for the ‘Sleep for 5 Seconds’ task is as below. You will need to add a ‘using’ to ‘System.Threading’ at the top of the script.

public void Main()
{
    // set the result value to be the parent value plus some changes
    Thread.Sleep(5000);

    // return success as the task result
    Dts.TaskResult = (int)ScriptResults.Success;
}

Finally, we can return to the parent package and complete the details required in the ‘Execute Child Package’ tasks, as per the screenshot below.

Executing the parent package, following the 5 second sleep, results in the following two message boxes popping up simultaneously.

From this we can see that both child tasks executed in parallel, both could access variables in the parent package and could set values in the parent package and that the parent package itself could see those changes.

Going back to the initial code reuse problem this means that script tasks can be moved into child packages and can pick up in scope variables values from the executing parent and return values back for further consumption within the parent package. Brilliant.