SSIS Passing ObjVar from Child back to Parent

It’s straightforward to use Parent variable configuration to alter a child package variable using the Execute package.

A bit trickier is to do the other way around. Let’s see how…

Let’s assume we have a SSIS project with the following:

ParentPkg.dtsx

With the following vars:

And

ChildPkg.dtsx

With the following vars:

And as simple Parent Var configuration to get the value of TargetMonth

How does it work:

The flow is the following:

1. ParentPkg runs:
1.1. it sets the value of the TargetMonthEnd
1.2. it calls the child passing it
1.3. The ChildPkg runs:
1.3.1. Get the value of TargetMonthEnd from the parent
1.3.2. Calculate the value of TargetMonthEndChild
1.3.3. Copy it to TargetMonthEnd
1.4. Print both values

some snippets from the code by section:

1.1

<PRE>
Dts.Variables["TargetMonthEnd"].Value = 20140131;
</PRE>

1.3.2

<PRE>
int TargetEndMonth = Convert.ToInt32(Dts.Variables["User::TargetMonthEnd"].Value);

Dts.Variables["User::TargetEndMonthTextChild"].Value = string.Format("[ {0} ] ", TargetEndMonth);
</PRE>

The most relevant part is
1.3.3

<PRE>
public void Main()
{
try
{
Variables vars = null;
if (Dts.VariableDispenser.Contains("User::TargetEndMonthText")
&& (Dts.VariableDispenser.Contains("User::TargetEndMonthTextChild")))
{
Dts.VariableDispenser.LockForWrite("User::TargetEndMonthText");
Dts.VariableDispenser.LockForRead("User::TargetEndMonthTextChild");
Dts.VariableDispenser.GetVariables(ref vars);
vars["User::TargetEndMonthText"].Value = vars"User::TargetEndMonthTextChild"].Value;
vars.Unlock();
}
}
catch { }
Dts.TaskResult = (int)ScriptResults.Success;
}
</PRE>

In this way we can run the child as standalone without breaking it in case it’s not called by a Parent.

The way it works behind the scene is (probably) the parent when using the Execute Package tasks, the Parent and the Child share the same collection of vars (passed by reference) so they can modify them.

Test and Execution

Let’s ‘unit test the Child’ and then go for AN integration test:

As expected the child just pick the default value when called standalone

Now let’s run the Parent with Child package enabled

And to double check … with child disabled

So the rule is

to match the share 2 vars and

to have an extra var in the child that will do the trick.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s