10 December 2014

Usefull Tips in SSIS (Part I)

Tip Execute Package Task

How many times do we need to create several “Execute Package Task”, and need to create several components, for example in an extraction package?

Do you remember the boring steps to create each one?

  • Create the “Execute Package Task” component;

  • Open the component: double click it, or mouse right button and “Edit”;

  • Change the original name in “General” tab;

  • In the “Package” tab, fill the “Connection” and the “Package Name”;

  • And, finally “OK”.

Now, imagine all doing this thirteen times, for the example above. Can we do it in an easy and faster way? Sure we can! We can create the first component, and create all the others with just a copy/paste, and renaming it. But, if we rename it, how it will call the new package we need? Following the next steps, when we create the first “Execute Package Task” component, we will be preparing it to be used in the future executing different packages, with minimum effort:

  • Create the “Execute Package Task” component.  

  • Open the component: double click it, or mouse right button and “Edit”;

  • Change the original name in “General” tab;

This is a very important step! In this step, the “Name” should contain the name of the package we pretend to execute. We can add a prefix. In this example the prefix is “EPT - ”

  • In the “Package” tab, just fill the “Connection”;

  • In the “Expressions” tab, open the expression;

  • In the “Property Expressions Editor”, choose the property “Package Name”, and open the “Expression Builder”;

  • In the “Expression Builder”, write the expression: REPLACE(@[System::TaskName], "EPT - ", "");

  • Evaluating the expression, the evaluated value is the name of the package we want to execute;

  • Click OK;

  • Then OK again, and close the “Execute Task Editor”.

Now the component as pink triangle in the top left corner, to inform that it as an expression in its definitions.

From now on, when we need to create new “Execute Package Task ”, we just need to copy this one and change the name. Automatically, using the expression, it will reference the package we defined in the name. Hope this will be useful for you.

.
.
.
.
.
.
       Rui Xavier
       Consultant