Home > SSIS > Execute a package from another package with variable – SSIS

Execute a package from another package with variable – SSIS


It is just executing a package stored in SQL Server or File system from a root package. Perhaps, you can say parent – Child package.

Execute Package Task

This task helps SSIS developer to design a complex package into multiple chunks of packages. It gives solution a big problem with multiple small tasks.

There are other interesting things you can learn in MSDN. http://msdn.microsoft.com/en-us/library/ms137609.aspx

Purpose of this article

Create a package and calling another package is very simple by placing Execute package task and point to the SSIS package in SQL Server or File system.

To make this article more interesting, I am going to explain “How to pass a value from the parent package to child package.

Implementation

Step 1: Create a package Name Parent.dtsx and create another package name child.dtsx

Step 2: Create a String variable name VarParent and assign “Hello Child” value in the parent package and assign variable scope to package level. I assume you know how to create package variable with scope. No worries learn now  http://msdn.microsoft.com/en-us/library/ms141670.aspx

Packages and Variable definition

Step 3: Add Execute Package Task in to parent package and connect to child package

Edit the Task and go to Package tab

  1. Assign File System value to the Location property
  2. Assign Child.dtsx package path to the connection property. It will create a connection in the package connection manager.

Step 3: click OK and open child package and add a script task in to the package.

Step 4: Add a String variable to the child package with package scope

Step 5: Edit the Script task to show the value in a message box while execution.

Step 6: Assign parent package variable value to child package variable

  • Open Package configuration wizard

  • Enable package configuration and Click Add button

  • Click next and go to package configuration type and select Configuration Type “parent package variable” and add the parent variable manually into parent variable textbox. Click next

  • Select the target property to Child package variable value. Click Next

  • Click Finish button to complete the package configuration with default configuration name.  You can also give your any name to package configuration.

Step 7: Click Close button.

Step 8: Execute the Parent package

Note: Package variables are case sensitive.

Thanks for reading. Please rate this article.

Advertisements
Categories: SSIS Tags: ,
  1. abhishek
    June 8, 2011 at 12:37 am

    Excellemt work man…………

  2. Unais
    June 11, 2013 at 3:04 am

    Well done man…. Keep going…

  3. GermanZ
    November 28, 2013 at 4:45 am

    Can you still run the ChildPkg without the parent variable?
    What I mean is, is there a way to bypass that variable IF not found in ParentPkg and use instead a variable within the ChildPkg?

    For example, I have a ConnectionString as a variable within the ParentPkg, the ChildPkg uses that variable, but for ever reason I need to run the ChildPkg individually, the pkg will fail because it cannot find the ConnectionString value.

    Thanks.

  1. February 5, 2014 at 1:41 pm

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: