How to execute a package from another package?

I hear you. You’ll use the 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.

Demoland!

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…

Zip It

In this post, I looked at using Execute Package Task to call a package from another and pass variable from parent to child.

@SamuelVanga

Change Package Properties Using Parameters–SSIS 2012

In DTS, changing package properties was close to impossible (the less we talk about DTS, the better). Package configurations introduced in 2005 solved most of the problems. In 2012, SQL Server Integration Services introduces parameters to make this process even easier. Parameters allow you to change package properties without the need for configuration files.

In this example, we will create a variable and change it’s value at run time using parameters.

Let’s enter the Demoland!

In a SSIS package, I created a variable called PromptValue. I assigned the value “I’m Variable” to it.

Next, I added a script task to the package, entered PromptValue as a read only variable, and used the following script.

MsgBox(Dts.Variables(“PromptValue”).Value.ToString)

I see the following message box when I execute the package.

Now, we want to be able to change this value during execution. Here is where we would create a package configuration and add this variable to it, so we can simply change the configuration file without editing the package.

But this time we will use parameters. I added a parameter called ParmPromptValue and assigned “I’m Parameter” as the value. Parameters are of two types: Package and Project. Package parameters can be used within the package. Project parameters can be used by all the packages in a project.

I created a package parameter.

Next, we’ve to pass the value of this parameter to the variable we created earlier. We will use expressions to do that. We know we can use other variables in the expressions builder. In addition to that, we can use parameters. I dragged the package parameter @[$Package::ParmPromptValue] to the expression window.

What should we see when I execute the package? Should it be [I’m Variable] or should it be [I’m Parameter]?

Since we used an expression to the variable, the value of the parameter overwrites the value of the variable.

We’ve so far executed the package from Visual Studio. In the below image, I deployed the package to SSIS catalog and assigned a different value to the parameter. More about deployment in the next post.

This is what I see when I run package from SSIS Catalog (Server).

Why in the world would you want to change the value like in this example, you might ask. I agree. This is a fictitious example, but think of connection strings, database names, user name/passwords that you can’t live without changing when moving packages through environments.

I’ve provided an introduction to parameters in this post. Be sure to read the following for more information. Parameters in Denali by Jamie Thomson and this from TechNet Wiki.

Resources

Download the sample package from here.

~Sam.

Follow Sam on Twitter – @SamuelVanga

Execute T-SQL Statement Task

I spotted a new task in SSIS 2012 – Execute T-SQL Statement Task. It executes T-SQL statements, only Transact-SQL statements against SQL Server databases. Cool, but don’t get too excited. Why? Because you can neither parameterize the SQL nor save the results to a variable.

The task editor looks like below:

Only plain and static scripts can be executed, for which Execute SQL Task can be used anyway. So, I don’t see a specific need for this task and I don’t plan on using it in the near future. Do you agree?

~Sam.

Update: Please note, this task isn’t new in 2012. It existed in previous versions. I apologize for the incorrect information. Thanks to Devin Knight (b|t) for correcting me.