Passing a values back from a child package to a parent package in SSIS
This is an example of something similar to what I saw Allan Mitchell execute in an SSIS package about a year ago. I didn't think much of it until a few days ago when I needed to pass a bunch of data from a child package back to the calling parent package. This example passes a record set from a child package to the parent package where the parent package loops through object. This example uses the Adventure Works database to push data from sys.tables table into a record set in the child package then push this information into a Object in the Parent Package. There are a few things that I will mention up front that have to be in place to make this work.
- The name of the Object in the parent package has to be different from the name of the Object in the child package
- You have to place the name of the parent Object in the script task's ReadWriteVariables line in the child package.
- The child package cannot contain a local variable with the same name as that of the Object variable from the parent package
- This looks to pass the variables by reference, and I'm not exactly sure why this is possible since I never assigned this variable from the parent package using package configurations. If anyone one has any information I would like to know how and why the parent packages variables are aviable to the child package? It does work which is the main reason I'm making this post.
Here we go:
This is a screen shot showing the layout and variables of the parent package, you can see the first TestObject Object and the first thing I do is call the child package.
This is a screen shot of the child package showing the layout and the variables. Please note the TestedObject Object and the lack of a variable called TestObject
Execute SQL task selects the top 10 name values from the sys.tables table in the adventure work database. This data is placed into the Child Package variable TestedObject
Screen Shot 3
Execute SQL task assigning the record set to the TestedObject.
The Script task Script page. Notice the readOnlyVariables has the TestedObject and the ReadWriteVariables line has the TestObject variable from the parent package. (This variable was not declared in this package)
The script task script itself does a simple assignment from the TestedObject to the TestObject variables.
This last screen shot shows the variable Test which was assigned to the result set in the for loop to display each row of the record set TestObject.
This is a screen shot of the package executing creating message boxes for each row in the sys.tables record set.
I found this process very useful to pass metadata about versions of data imported into a master data management system from a child package back to a parent package. You can pass any data types from a child to a parent using this method. Normally I put the script that passes the value from the child to parent package in the post execute event handler. The only reason I put the task in the event handler is for style, as I feel it’s better to put this variable handling code separate from the specific package logic itself.
Well that’s it let me know what you think.