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.
   
2. Open the “Execute Package Task Editor” (double click the component), go to the “Expressions” tab and click on the “…” icon.
   
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
      
4. For the “Connection” property, edit the expression (click “…”) and insert:

   @[User::DBorFilesystem] == "DB"  ? "": @[System::TaskName] +".dtsx"

   where  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.

http://biatlink.wordpress.com/2013/11/22/ssis-dynamic-location-for-execute-package-task/