Skip to content
November 22, 2013 / Rodrigo Chiolas

SSIS – Dynamic location for Execute Package Task

Problem

On a recent project, with a SSIS component, i needed to alternate between the  visual studio solution (filesystem) and the deployed solution (DB). The problem was the bazillion of child packages, Execute Package Tasks,  that the project had. For those not familiar with SSIS, “Execute Package Tasks ” component allows to call other packages, either from filesystem or DB.

Naturally, i don’t want to manually change from DB to filesystem, and vice versa, every time i switch enviroments. So i needed to find a solution to dynamically execute those child packages.

Solution

To provide a way to quickly, and easily, change enviroments we gonna use expressions of the “Execute Package Tasks ” component.

Steps:

  1. Create a variable, called DBorFilesystem, that will be used to alternate between the enviroments. The variable will have the values DB or Filesystem.
    create_variable
  2. Open the “Execute Package Task Editor” (double click the component), go to the “Expressions” tab and click on the “…” icon.
    open_expressions
  3. Create 2 properties expression:
    1. Connection – to alternate between the db connection and the package connection
    2. PackageName – to specify which package from db should be executed
      create_expressions
  4. For the “Connection” property, edit the expression (click “…”) and insert:
    @[User::DBorFilesystem] == "DB"  ? "<db_connection_name>": @[System::TaskName] +".dtsx"

    where <db_connection_name> is the db connection name where the packages are. If the component name it is different of the name of the package connection, replace @[System::TaskName] +”.dtsx” with the name of the package connection.

  5. For the “PackageName” property, insert:
    @[User::DBorFilesystem] == "DB"  ? "\\"+ @[System::TaskName] :""

    If the component name it is different of the name of the package , replace @[System::TaskName]  with the name of the package (without the extension “.dtsx”).

And thats it.

If you have multiple levels of child packages (package calling package that call package and so on), configure the variable “DBorFilesystem” on a package configuration, either a xml file or a sql server. Try not to use parent variable, because parent variable configuration are executed after the package validation, which means that the package will be validated with the default value of the package variable DBorFilesystem, not with the parent variable value.

Regards,

Advertisements

5 Comments

Leave a Comment
  1. Johnny / Feb 23 2014 7:06 pm

    This is awesome. Thanks for the post. I was about to create a hilariously manual process for this.

  2. Joe / May 15 2014 4:23 pm

    How to do this in SS 2012?

    • Rodrigo Chiolas / May 22 2014 11:07 am

      Hi Joe,

      I didn’t try in SS 2012 but i think it works the same way.
      Im out of time right now but if i get some i´ll try with SS2012.

      • Joe / May 22 2014 1:14 pm

        Thanks for the reply. I think I’ve ran out ways to try to make it work in 2012 as it did in 2008.

  3. Peter Lee / Feb 18 2015 9:06 pm

    Replacing the variable with a parameter will make the package be configured at run-time. That’s my suggestion.

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: