SSIS - How to set parent variable value from child package using Package Deployment Model

Question

I'm trying to pass child variable value to parent but could not get it to work. I'm using Package Deployment Model not Project Deployment Model.

The variables from Parent and Child have exactly the same name. I referenced the parent variable in child package using package configuration.

Then I set the value in child script task:

The value is set correctly(from 0 to 1):

But back to parent, the value is still 0(expecting 1):

Since I'm using Package Deployment Model, so I could not add parameter binding.

My assumption has always been that this only works from parent > child. It's never been able to pass values back from child to parent. — Nick.McDermaid
Even if you were using the Project Deployment Model, that only simplifies the passing of arguments to the child process. Package Parameters are read only within the child package so no opportunity to send information back to the parent. — billinkc
The longer I think about this, I stole a trick from Andy Leonard where the child package would modify the parent package variable (it was returning a result set) but I bet the trick is that the parent package's variable was of type Object. Which could make sense, as the object reference would be passed to the child package whereas anything that isn't Object type would be passed by value. You're welcome to see if the session has been published to the PASS youtube channel. It was the 2009 Summit. — billinkc
So yes you can do it, but is it a design smell? If you're trying to use a child package like a function that returns values, I suspect there is some design issue here. — Nick.McDermaid

billinkc billinkc · Accepted Answer · 2020-05-08T03:28:39

tl;dr;

Remove your child package's HasFrontOrderDataFile. The child package won't be able to run by itself and many tasks will warn and/or show red Xs indicating they're broken but when the parent runs, they will work.

the setup

Given a parent package with 4 variables: Col1, Col2, ParentVariableSimple all as Int32 and ParentVariableObject

Before

This is an Execute Script Task that merely echos variable values. Add all 4 as read only variables for this task

bool fireAgain = false;
string message = "{0}::{1} : {2}";
foreach (var item in Dts.Variables)
{
    Dts.Events.FireInformation(0, "SCR Echo Back", string.Format(message, item.Namespace, item.Name, item.Value), string.Empty, 0, ref fireAgain);
}

Execute Package Task

This is going to run a Child package. I defined a file connection manager to point to Child.dtsx

FELC Shred ParentVariableObject

This is a standard ADO Recordset enumeration pattern. I am going to retrieve the values from ordinal position of 0 and 1 of ParentVariableObject.

After

This is the same Script Task echoing out the values of 4 variables.

Child package

My child package is defined as

I have two variables defined here: ChildVariable as int and ChildObjectVariable as object (and not used). ChildVariable is initialized to -2. If it flips to -1, then we know we've pulled the parent value correctly.

In Package Configuration, I have mapped User::ParentVariableSimple into ChildVariable's Value property.

SCR Echo

The same script task used in the parent package. I pass both Child variables into it.

Scr Increment by 10

Here's where things go weird ;)

I pass in two ReadWriteVariables: User::ChildVariable,User::ParentVariableSimple

Now, you can't select ParentVariableSimple from the UI there, you have to correctly type the variable as it exists in the parent package. Within, my code is similar to above.

        foreach (var item in Dts.Variables)
        {
            item.Value = (int)item.Value + 10;
        }

At this point, we're modifying the value in the parent package!

SQL Load parent variable

I am going to create an in memory data set via query here. Assuming OLE DB connection manager, configure it

ResultSet = Full result set
SQLStatement = SELECT ? AS Col1, ? AS Col2;

Parameter Mapping

User::ChildVariable - ParameterName 0
User::ParentVariableSimple - ParameterName 1

Result Set

Result Name 0 - Variable Name - User::ParentVariableObject

Results

The output of the run is shown below. In the FELC, Col1 will be the child variable's value and Col2 will be the modified value of the parent variable which is also conveniently echoed.

SSIS package "C:\Users\bfellows\source\repos\PackageDeploymentModel\PackageDeploymentModel\Parent.dtsx" starting.
Information: 0x0 at Before, SCR Echo Back: User::Col1 : 0
Information: 0x0 at Before, SCR Echo Back: User::Col2 : 0
Information: 0x0 at Before, SCR Echo Back: User::ParentVariableObject : System.Object
Information: 0x0 at Before, SCR Echo Back: User::ParentVariableSimple : -1
Executing ExecutePackageTask: C:\Users\bfellows\source\repos\PackageDeploymentModel\PackageDeploymentModel\Child.dtsx
Information: 0x40016042 at Child: The package is attempting to configure from the parent variable "User::ParentVariableSimple".
Information: 0x40016042 at Child: The package is attempting to configure from the parent variable "User::ParentVariableSimple".
Information: 0x0 at SCR Echo, SCR Echo Back: User::ChildObjectVariable : System.Object
Information: 0x0 at SCR Echo, SCR Echo Back: User::ChildVariable : -1
Information: 0x0 at After, SCR Echo Back: User::Col1 : 9
Information: 0x0 at After, SCR Echo Back: User::Col2 : 9
Information: 0x0 at After, SCR Echo Back: User::ParentVariableObject : System.__ComObject
Information: 0x0 at After, SCR Echo Back: User::ParentVariableSimple : 9
SSIS package "C:\Users\bfellows\source\repos\PackageDeploymentModel\PackageDeploymentModel\Parent.dtsx" finished: Success.

Final thoughts

This is a hack. It works, yes. Those that maintain this package after you will curse your name until the end of time. A cleaner approach would be to ... raise an event within the child package. Record that run's value to a table and have the parent read it out. Probably something else but this is just the technical answer to the question.