THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Eric Johnson

My Thoughts on SQL Server and Database Related Technologies.

Calling Child Packages in SSIS

Calling an SSIS package from another SSIS package is, on the surface, a simple task. You just use the Execute Package Task and point it to a package on a SQL Server or somewhere in the file system, as shown below.

clip_image002

It is pretty simply right? But what if you need the parent package to pass information to the child package? That is accomplished by using variables in the parent package and package configurations in the child package. Let’s look at an example. I have created two packages, one named Parent and one named Child. In the parent package I have added a variable call MessageToChild as shown.

clip_image004

Then I added the execute package task we looked at earlier to call the child package. Within the child package, I added a variable called Message as shown below.

clip_image006

Okay, the next step is to get the Parent variable value into my child variable; this is where package configurations come into play. In the child package, I added a Parent Package Variable package configuration which uses the MessageoChild variable to populate the child’s Message variable.

clip_image008

clip_image010

When the Parent package calls the Child package, the variable value is passed. Now for a quick test, I added a script to the child package with the following code:

MsgBox(Dts.Variables.Item("Message").Value.ToString, MsgBoxStyle.OkOnly, "Message From Parent")

This will pop the following message box when the parent package is run and subsequently calls the child package.

clip_image012

Any values that you want to pass have to be stored in variables in the parent package. The child package on the other hand can use those values to set any property with a compatible data type. You can set file paths, expression values, connection strings, and, as we saw, variable values. Obviously there are other ways to get packages to “talk” to one another, like writing data to a table or file, but this is a quick and efficient way to get a parent package to send simple bits of information to child packages.

Published Thursday, October 28, 2010 12:48 PM by ejohnson2010

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Price said:

Another way of referencing a variable in a child package is to simply use the variable name used in the parent without the User:: / System:: Prefix. I'm not quite sure if that's a supported scenario since I rarely see people mention it.

October 29, 2010 3:55 PM
 

Jimmy said:

The challenge for me has been the fact that we do our development on the file system but our deployment is to the package store using msdb.  The attributes are different in calling child packages depending on where the package source is and there is not a good way to dynamically set it.

October 29, 2010 6:13 PM
 

paulhutagalung said:

>The challenge for me has been the fact that we do our development on the file system but our deployment is to the package store using msdb.  The attributes are different in calling child packages depending on where the package source is and there is not a good way to dynamically set it.

Just use expression for that. and if you need to run by designer it can automatically switch to file system

October 31, 2010 12:03 PM
 

Joao Silva said:

Eric, i have tried do use the calling chil packages as a solution for a problem but it did not work. My problem is to use OLE DB SOURCE dynamically with different tables in the same job. I have created a variable in data acess mode which contain the name of table to be used. In my loop when i went for the second table the job abends. Any idea to solve this? tks Joao Silva

November 3, 2010 11:42 AM
 

ejohnson2010 said:

Joao: The problem is likely caused by the schema of the tables being different. Your OLE DB data source has its schema defined at design time and when you dynamically change tables at run time, you will get a schema mismatch. Without knowing your specific details, it is hard to make a recommendation, but you might have to do some processing in t-sql to move data to a staging table and then use SSIS to process from the staging table. That way the schema is a known element.

November 3, 2010 12:19 PM
 

Joao Silva said:

Eric, first of all thank you for your answer. I am starting now in SSIS tasks and your help is really usefull :)

I have created a loop which the driver is a table where each line has 2 columns: TABLE_NAME (contains each table name) and SQL_COMMAND where i have the sql instruction: "SELECT * FROM TXXXX"   XXXX - is the name of the table.

example DRIVER TABLE:

TABLE_NAME  SQL_COMMAND

TW4A0       SELECT * FROM TW4A0

TW4B0       SELECT * FROM TW4B0

I call the child package passing this 2 columns:

In the child package i use OLE DB SOURCE where i want to use the variable SQL_COMMAND with the value "SELECT * FROM TXXXX"

this is just to migrate to another database thru OLE DB destination where i use the value of column TABLE_NAME to load data.

tks

João Silva

November 3, 2010 12:59 PM
 

Mark Zatx said:

Just have to say many thanks for this. Very clear and concise instructions.

September 18, 2011 11:51 PM
 

Uday said:

Simple but excellent to Understand.

lot of thanks,

June 10, 2013 3:48 PM
 

asdf said:

asdf

September 15, 2014 4:19 PM

Leave a Comment

(required) 
(required) 
Submit

About ejohnson2010

Eric (Microsoft SQL MVP) is the co-founder of Consortio Services, and the primary Database Technologies Consultant. His background in Information Technology is diverse, ranging from operating systems and hardware to specialized applications and development. He has even done his fair share of work on networks. Since IT is really just a way to support business processes, he has also acquired his MBA. All in all, he has 10 years of experience with IT, a great amount of which has been working with Microsoft SQL Server. Eric has managed and designed databases of all shapes and sizes. He has delivered numerous SQL Server training classes and webcasts as well as presentations at national technology conferences. Most recently, he presented at TechMentor on SQL Server 2005 Replication, Reporting Services, and Integration Services. In addition, he is active in the local SQL Server Community, serving as the President of the Colorado Springs SQL Server Users Group.
Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement