SSIS Design Pattern: Access Parent Variables from a Child Package in the SSIS Catalog

I am regularly asked about passing variable values between SSIS packages.

Tim Mitchell (Blog | @Tim_Mitchell) wrote an excellent Notes from the Field article for the SQL Authority blog called SSIS Parameters in Parent-Child ETL Architectures. Before the first version of the SSIS Catalog was even released, Jamie Thomson blogged about The new Execute Package Task in SSIS in Denali and Eric Johnson blogged about Calling Child Packages in SSIS here at SQLBlog. In pre-SSIS Catalog days, I blogged about this topic at SQLBlog: Accessing Parent Package Variables In Child Packages… Without Configurations. There are many other posts related to accessing variable values in SSIS.

This topic has received a fair amount of treatment.

“Why are you writing about this again, Andy?”

I’m glad you asked! I’ve learned something new about the behavior I described in my 2007 post. I’ve discovered a difference in the way SSIS parent-child package variable values interact in the SSIS Catalog and in SSDT-BI – the designer.

Disclaimer: To be fair, the behavior I described in 2007 was not a design feature of SSIS. It worked, but it was (and is) a by-product of the way the original Execute Package Task was designed. I was given fair warning by the SSIS team at Microsoft that there were no guarantees this functionality would exist in future versions of the product.

Another disclaimer: I am not picking on Microsoft! My intent in writing this is to inform those who read my 2007 post that this functionality still exists but is reported differently than some might expect, and to give you some sense of how things are different. Cool? K. Let’s move forward.

Show and Tell

Let’s build a demo SSIS project. I called mine ParentChildTests and added two SSIS packages named EPTParent.dtsx and Pkg1.dtsx:

In the EPTParent.dtsx SSIS package, I add a package-scoped string variable named ParentVariable (creative name, I know!) with some default value:

In the Pkg1.dtsx SSIS package, I add a Script Task configured to read the ReadOnlyVariables User::ParentVariable and System::PackageName. The Script Task contains the following code:

                        string packageName = Dts.Variables[“System::PackageName”].Value.ToString();
            string msg = “I am ” + packageName;
            bool fireAgain = true;

            Dts.Events.FireInformation(1001, “Script”, msg, “”, 0, ref fireAgain);

            string parentVariable = Dts.Variables[“User::ParentVariable”].Value.ToString();
            msg = “Parent Variable: ” + parentVariable;

            Dts.Events.FireInformation(1001, “Script”, msg, “”, 0, ref fireAgain);

The purpose of the Script Task is to generate a couple log messages by raising Information events. Please note: there is no variable in Pkg1.dtsx named ParentVariable! If you create a variable named ParentVariable in Pkg1.dtsx, this demo will not work. (You can validate the script before closing the VSTA window. I show you how here!)

You can test-execute Pkg1.dtsx to see if it fails (it should fail):

“Wait, what, Andy? You’re teaching us how to write SSIS packages that fail?” Yes. Yes, I am. On purpose, even. Why? Because you are going to write SSIS packages that fail. Teaching you anything else would be disingenuous. If failure bothers you, you may want to consider a different line of work.

The key here is the package should fail and return the error:

Failed to lock variable “User::ParentVariable” for read access…

If you see this error, you’ve done everything I described correctly (so far).

This error means there is a caveat: You cannot successfully execute this package stand-alone; to succeed, this SSIS package must be executed via the parent SSIS package (EPTParent.dtsx in this case).

In the EPTParent.dtsx SSIS package, I add an Execute Package Task configured to execute Pkg1.dtsx:

Before we test-execute EPTParent.dtsx in SQL Server Data Tools – Business Intelligence (SSDT-BI), I want to explain what I expect to happen. I expect EPTParent.dtsx to execute. I expect the Execute Package Task in EPTParent.dtsx to execute and, in turn, execute the Pkg1.dtsx SSIS package. I expect Pkg1.dtsx to execute – successfully this time – leveraging and undocumented (and unsupported) feature in the SSIS Execute Package Task that allows child packages to access parent package variables.

Now you may be thinking, “Big deal, Andy. I can just use the Parent Package Variable Package Configuration to read the value of a parent package in a child package.” And you would be correct in your thinking. But I was very careful about the words I wrote just now, and I wrote “access.” Why didn’t I just write “read” since that’s all we’re doing here? Because it’s possible, using this very functionality, to also write a value from a child package to a parent package. That’s not possible using a Parent Package Variable Package Configuration.

Taking a look at the Progress / Execution Results tab of Pkg1.dtsx, we see exactly what I thought I would see – a successful execution and an information message reporting the value of ParentVariable:

“Why Are You Telling Me This?”

Again, I’m glad you asked. So far, all I’ve showed you is what I wrote about in 2007.

Here’s what’s changed. If I deploy this project to the SSIS Catalog execute it, and view the Catalog reports, I will not see two package executions. Instead I will see a single package execution for EPTParent.dtsx:

The Execution Overview table on the Overview report provides some clarity – especially the Task Name column:

Clicking the View Messages link shows even more detail, and the last few messages (sorted  as last-occurred, first-listed) demonstrate Pkg1’s Script Task executed “inside” Pkg1, and provides some evidence that Pkg1 executed “inside” the Execute Package Task in the EPTParent.dtsx SSIS package:

 

“Trivial?”

Is this merely trivial or anecdotal? As with so many other questions in the data field, the answer is “it depends.”

If you need (or merely desire) visibility into your enterprise data integration lifecycle, my answer would be “no, this is not trivial or anecdotal.” Child package executions – when the child is executed via the Execute Package Task – are hidden from the “All Executions” report view. An SSIS package executed and it doesn’t show up. If you understand why it doesn’t show up, this is no big deal. But the report is not titled “Most Executions,” is it?

“Why does this happen?”

I go into more detail about this behavior in my article Advanced Event Behavior – Level 10 of the Stairway to Integration Services – part of the Stairway to SSIS series at SQL Server Central. That article includes this image (Figure 43), my attempt at constructing a visual representation that SSIS will never produce in real life. A Parent package – named Parent.dtsx – executes. It contains an Execute Package Task that executes a package named Precedence.dtsx. To construct this image, I used two screenshots of the Package Explorer tab – one from Parent.dtsx and one from Precedence.dtsx:

This is one representation of what happens when a child SSIS package is executed from a parent SSIS package. It shows the child SSIS package running “in scope” of the Execute Package Task, and I believe that is an accurate representation. And I think that’s why we do not see the child package executions listed on the All Executions SSIS Catalog report.

:{>

Learn more:
SSIS Design Patterns training in London, UK, 7-10 Sep 2015    
Advanced SSIS Training with Andy Leonard & Tim Mitchell in Reston Virginia Dec 7 – 10, 2015

Stairway to Integration Services
Linchpin People Blog: SSIS
Stairway to Biml