ssis

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


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.
CHILD PACKAGE

Then I set the value in child script task:
CHILD

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

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

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

Solution

  • 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

    Parent package

    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

    enter image description here

    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

    Parameter Mapping

    Result Set

    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.