execute a package in ssis

How to execute a package from another package?

Execute Package Task. This mechanism of executing one package from another is popularly knows as Parent Child paradigm. Let me tell you, this isn’t new in 2012. There are, however, a few changes to the Execute Package Task. Let’s take a look.

I added two packages to the project and conveniently named them Parent.dtsx and Child.dtsx.

Child Package

In the child package, I added a script task and used the following script. This will display a message box and tells that it is executing.

MsgBox(“Howdy! I’m the child package”)

Parent Package

In the parent package, I added an Execute Package Task and renamed it to Run Child Package.

In the Package page of the Execute Package Task editor, there is a new property called reference type. It is set to Project Reference by default. This means you can point the execute package task to another package within the same project. I selected Child.dtsx from the drop down.

The following is the output when I execute the parent package.

In prior versions, you’ll have to choose either file system or SQL Server as the source for child package and have connection managers to all the child packages in the parent. For example, if you have one parent package calling 50 child packages, the parent needs 50 connection managers to each of those child packages. This is still supported for legacy packages – change the reference type property to External Reference.

Passing a variable from parent to child

You often pass variables from parent package to the child package; connection strings, parent package start time, parent package name (ok. May be). Again, in the previous versions, you would use a parent package configurations to do this.

In this example, I’ll pass parent package name to the child package.

Another change to the Execute Package Task is parameter binding. You can map a parameter or a variable from the parent package to a parameter in the child package.

In the below image, I created a parameter in the child package. I wrote about SSIS parameters in an earlier post.

From the parent package, open the Execute Package Task Editor. In the parameter bindings page, I mapped the child parameter to the parent’s PackageName system variable.

Then I changed the script in the child package as follows:

MsgBox(“Howdy! I’m the Child Package. I was called by ” & Dts.Variables(“$Package::ParentName”).Value.ToString)

When I execute the parent package, I see…

SSIS 2012: Better Execute Package Task

Execute Package Task had some rough edges in previous versions of SSIS. to name a few, you had to create a connection manager to be used by each task/package, and there was not a simply way to parameterize the connection managers all at once. Passing values from parent to children packages was something that did not have a good story either.

A plethora of connection managers in the old days...

What has changed in SSIS 2012?

With SSIS 2012 when you use the new project deployment model, the Execute package task is now easier to setup and configure:
For one, we just need to select the name of the package – any package within the current project – from a drop down list, as we will save quite a few clicks a we don’t need to create a configuration manager each time.

Point and click

Second, we have the ability to bind the parameters of the child package to variable or parameters in the parent package, and once again, just by selecting the appropriate values from the dropdown lists.

Nice: The execute package task 'sees' the parameters defined in the child package

Third, there are no connection managers to maintain and configure.