Passing an Object from a Parent Package To a Child

Occasionally, you may run into the need to pass values between packages. In most cases, you can use a Parent Package Variable configuration to pass the value of a variable in the calling (or parent) package to the called (or child) package. However, Parent Package Variable configurations can’t be set up on variables of type Object. Fortunately, you can work around this pretty easily, thanks to the fact that the calling package variables are inherently accessible in the called packages. 

I’ve set up a sample parent and child package to illustrate this. The parent package is straightforward. It uses an Execute SQL task to populate an object variable named TestVar with a recordset object, and then calls the child package.

image image

The child package has a Foreach Loop Container to iterate over the recordset object. It has a Script task that is used to copy the parent package’s variable (TestVar) to a local variable named LocalVar. This is the variable that the Foreach Loop is configured to use. Why copy the value? If you don’t have a local variable to reference in the Foreach Loop, it won’t validate properly.

image image

The script in Copy Variable is pretty simple. It relies on the fact that you can reference parent package variables inherently, as they are included in the collection of variables accessible in the local package. The script just copies the value from one variable to the other, so that the Foreach Loop will have something to do.

public void Main()
{
    Variables vars = null;
    Dts.VariableDispenser.LockForWrite("User::LocalVar");
    Dts.VariableDispenser.LockForRead("User::TestVar");
    Dts.VariableDispenser.GetVariables(ref vars);

    vars["User::LocalVar"].Value = vars["User::TestVar"].Value;
    vars.Unlock();

    Dts.TaskResult = (int)ScriptResults.Success;
}

Please note that for this to work, you cannot have a variable in the child package with the same name as the variable in the parent package. If you do, the local variable will hide the parent variable. Outside of that, this works really well for passing object values between packages. The same technique can also be used in reverse to send values back to the parent package, if you have that need.

The sample has been uploaded to my Skydrive. Let me know if you have any questions.

15 Comments

  1. http:// says:

    Thanks for a nice way of passing object variable.
    Can we use this method for the variables other than object type also?

  2. jwelch says:

    Yes, you can use it for any variable type.

  3. Phil Brammer says:

    If you use this method for other variable types as a way to bypass parent package configurations, just beware that you can no longer run the child package alone; it must always be executed by the parent, otherwise it will fail because the referenced variable won’t exist.

  4. jwelch says:

    That’s true, Phil, but it’s pretty easy to add a conditional check and set a default value if the variable isn’t present.

    if (! Dts.VariableDispenser.Contains(“User::TestVar”))
    {
    //Set a default value
    }

  5. Dhiren says:

    Hi tried your solution, but when I am running it I am getting following error ..

    —————————
    A deadlock was detected while trying to lock variables “User::ParentVariable” for read access and variables “User::ChildVariable” for read/write access. A lock cannot be acquired after 16 attempts. The locks timed out.

    —————————

  6. Dhiren says:

    I got the solution.
    I was locking the variable in Script Task Editor by declaring it in ReadWriteVariables section.

    Thank you.

  7. Murali says:

    Hi welsh,

    I am using this method to copy an object type variable to my child package. I am passing this object type variable to 2 child packags. The variable names are different in each of these package. I am getting the error message mentioned below. What could be the issue.
    Error: COM error object information is available. Source: “ADODB.Recordset” error code: 0x800A0BCD Description: “Either BOF or EOF is True, or the current record has been deleted. Requested operation requires a current record.”.

    • jwelch says:

      This is occuring because you can’t iterate a recordset twice. You are passing the same recordset to two different packages, and I’d guess you are trying to read the recordset in both packages. The 2 variables are just pointers to the same underlying recordset object.

      ADODB.Recordset objects are forward only cursors – once you’ve enumerated the rows in it once, it’s done. I think you can call the Clone method on the recordset object in a script to create a second enumerable instance. The clone still points to the same underlying data. However, it’s been a while since I’ve done that, and I’m not sure if it works exactly as I’m describing it.

      • Blasto says:

        Is there a Reiterable RecordSet ? Cloning would require double memory. Why not use a reiterable recordset instead ? In java, there is a CachedRowSetImpl which is reiterable. Anything like that for C# and VB.NET ?

        • jwelch says:

          ADO.NET and ADODB both support recordsets that can be iterated multiple times. However, if you create the recordset using an Execute SQL task against an OLE DB connection, it uses the old ADODB technology, and the recordset produced is based on a forward-only cursor. As far as I know, you can’t override those settings in the Execute SQL task.

  8. Blasto says:

    Thanks ! The post and especially the comments saved the day for me.

    • Blasto says:

      As an aside, would your approach work if the child and parent packages were located on different servers. Is such a scenario possible in an enterprise environment ?

      Thanks.

      • jwelch says:

        It will work, as long as the environments can see each other. However, because of the way SSIS works, the child package will be executed in the parent package environment (nothing to do with the approach above – SSIS works that way with any parent-child package).

  9. Blasto says:

    Hi jwelch ! Thanks for the quick response. I have a question which is related to your post. I was wondering if you could suggest some solutions to my question. The link is at – http://social.msdn.microsoft.com/Forums/sqlserver/en-US/450ec296-71bb-4170-a71c-a4e3bf453bab/pass-variables-to-child-via-c-script-task-?forum=sqlintegrationservices#5c766826-ba38-4df6-b3b0-85b4b42914ed

    The question is reproduced below –

    I don’t want to use package configurations to pass variables from Parent to child package. I’d prefer to pass the variables to child, from a C# script task in the parent package. Can this be done ? Here is the pseudocode for what I hope to do –

    //code in script task of parent package

    Main(){

    Object parentObj = SSIS.ParentPackage.myObject;

    String parentStr = SSIS.ParentPackage.myString;

    callChildPackage(parentObj, parentStr);

    }

    Does this make sense ? Is there any flaw in doing things like this ?

    Thanks.

  10. jwelch says:

    Responded on the forums – but in summary, you can do this, but it means you have to take full control of the child package execution in your script.

Leave a Reply to http://