Advanced Execute Package Task

Task Factory Advanced Execute Package Task The Advanced Execute Package Task provides a way for you to execute a child package from your SSIS package as well as passing and retrieving variables to and from the child package. Feature Highlights Can be used to execute child packages on a file system or within SQL Server Can be used with password protected packages Allows for variables to be mapped from the parent package to be used in the child package Allows for the reading of variables from child packages to be used in your package

Advanced Execute Package Task

Advanced Execute Package Task- Step 1

The Advanced Execute Package task is used to execute child packages within an SSIS Package from either a local file or a package stored on SQL Server. A unique feature of the Advanced Execute Package is that the ability to map variables between the executing package and the child package.

The Advanced Execute Package task is used to execute child packages within an SSIS Package from either a local file or a package stored on SQL Server. A unique feature of the Advanced Execute Package is that the ability to map variables between the executing package and the child package.

Advanced Execute Package Task- Step 2

Setting up the Child Packages 

Child Package Location - There are two choice for choosing a child package location

• File System - A local file on the executing machine
• SQL Server - Package stored in SQL Server

Setting up the Child Packages 

Child Package Location - There are two choice for choosing a child package location

• File System - A local file on the executing machine
• SQL Server - Package stored in SQL Server

Advanced Execute Package Task- Step 3

Child Package Path - Depending on the selection made for the Child Package Location, the Package Path will point to either a local file or the path where the package is stored in SQL Server. Clicking the ellipsis button will either show a File Browser (File System mode) or our built in SQL Server Browser (SQL Server mode).

Password - If the child package is password protected, this is where you can enter the password.

Child Package Path - Depending on the selection made for the Child Package Location, the Package Path will point to either a local file or the path where the package is stored in SQL Server. Clicking the ellipsis button will either show a File Browser (File System mode) or our built in SQL Server Browser (SQL Server mode).

Password - If the child package is password protected, this is where you can enter the password.

Advanced Execute Package Task- Step 4

Variable Mappings

You have three options for mapping variables from your package to the child package called Assignment Directions.

1. Read variable from child package - This mapping direction will read the value of the variable selected in the "Child Package Variable" and assign the value to the variable selected in the "Parent Package Variable". The value of the variable will read after execution of the child package has occurred. 
2. Write variable to child package - This mapping direction will write the variable value selected in "Parent Package Variable" and assign the value to the variable selected in the "Child Package Variable". The value of the variable will be written before execution of the child package.
3. Read and write variable from child package - This mapping direction will both write the variable value selected in "Parent Package Variable" and assign the value to the variable selected in the "Child Package Variable" before execution occurs and then will read the value of the variable selected in the "Child Package Variable" and assign the value to the variable selected in the "Parent Package Variable" after execution has occurred.

Variable Mappings

You have three options for mapping variables from your package to the child package called Assignment Directions.

1. Read variable from child package - This mapping direction will read the value of the variable selected in the "Child Package Variable" and assign the value to the variable selected in the "Parent Package Variable". The value of the variable will read after execution of the child package has occurred. 
2. Write variable to child package - This mapping direction will write the variable value selected in "Parent Package Variable" and assign the value to the variable selected in the "Child Package Variable". The value of the variable will be written before execution of the child package.
3. Read and write variable from child package - This mapping direction will both write the variable value selected in "Parent Package Variable" and assign the value to the variable selected in the "Child Package Variable" before execution occurs and then will read the value of the variable selected in the "Child Package Variable" and assign the value to the variable selected in the "Parent Package Variable" after execution has occurred.

Advanced Execute Package Task- Step 5

Data Type Matching of Variables

The data type of the mapped variables must the same. For instance if you choose a string variable in the Child Package Variable, only string variables from your Package will be shown in the Parent Package Variable and available for selection.

Data Type Matching of Variables

The data type of the mapped variables must the same. For instance if you choose a string variable in the Child Package Variable, only string variables from your Package will be shown in the Parent Package Variable and available for selection.

Advanced Execute Package Task- Step 6

Variable Mappings Grid

After mappings are created, they are added to the variable mappings grid. If you need to delete a mapping, simply click the Delete button.

Variable Mappings Grid

After mappings are created, they are added to the variable mappings grid. If you need to delete a mapping, simply click the Delete button.

http://pragmaticworks.com/Products/Task-Factory/Features/AdvancedExecutePackageTask.aspx

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/

How to execute a package from another package

I hear you. You’ll use the Execute Package Task. This mechanism of executing one package from another is popularly knows as Parent Child paradigm. Let me tell you, this isn’t new in 2012. There are, however, a few changes to the Execute Package Task. Let’s take a look.

Demoland!

I added two packages to the project and conveniently named them Parent.dtsx and Child.dtsx.

Child Package

In the child package, I added a script task and used the following script. This will display a message box and tells that it is executing.

MsgBox(“Howdy! I’m the child package”)

Parent Package

In the parent package, I added an Execute Package Task and renamed it to Run Child Package.

In the Package page of the Execute Package Task editor, there is a new property called reference type. It is set to Project Reference by default. This means you can point the execute package task to another package within the same project. I selected Child.dtsx from the drop down.

The following is the output when I execute the parent package.

In prior versions, you’ll have to choose either file system or SQL Server as the source for child package and have connection managers to all the child packages in the parent. For example, if you have one parent package calling 50 child packages, the parent needs 50 connection managers to each of those child packages. This is still supported for legacy packages – change the reference type property to External Reference.

Passing a variable from parent to child

You often pass variables from parent package to the child package; connection strings, parent package start time, parent package name (ok. May be). Again, in the previous versions, you would use a parent package configurations to do this.

In this example, I’ll pass parent package name to the child package.

Another change to the Execute Package Task is parameter binding. You can map a parameter or a variable from the parent package to a parameter in the child package.

In the below image, I created a parameter in the child package. I wrote about SSIS parameters in an earlier post.

From the parent package, open the Execute Package Task Editor. In the parameter bindings page, I mapped the child parameter to the parent’s PackageName system variable.

Then I changed the script in the child package as follows:

MsgBox(“Howdy! I’m the Child Package. I was called by ” & Dts.Variables(“$Package::ParentName”).Value.ToString)

When I execute the parent package, I see…

Zip It

In this post, I looked at using Execute Package Task to call a package from another and pass variable from parent to child.

Sending mail within SSIS - Part 3: Execute SQL Task

Case
I want to send mail within SSIS, preferably HTML formatted. What are the options?

Solutions

There are a couple of solutions to mail within SSIS:

1. Send Mail Task
2. Script Task with SmtpClient Class
3. Execute SQL Task with sp_send_dbmail
4. Custom Tasks like Send HTML Mail Task or COZYROC

To demonstrate the various solutions, I'm working with these four SSIS string variables. They contain the subject, body, from- and to address. Add these variables to your package and give them a suitable value. You could also use parameters instead if you're using 2012 project deployment.

Add these four variables to your package

C) Execute SQL Task
The Execute SQL Task solution uses a stored procedure from SQL Server. To use that you first have to configure database mail in SSMS.

1) Database Mail Wizard
Open SQL Server Management Studio (SSMS). Go to Management and then to Database Mail.

Database Mail

2) Enable Database Mail
If Database Mail isn't available it will ask for it. Choose the first option to create a profile.

Enable Database Mail and create profile

3) Create Profile
Enter a name and description for the mail profile. You will need the name in the stored procedure later on.

Create a mail profile

4) New Database Mail Account
Click the Add button to create a new database mail account. This is where you configure the SMTP server and the FROM address.


Configure SMTP and FROM address

Account ready, continue wizard

5) Public / Private
Make your profile public (or private)

Public profile

6) System Parameters
Configure the System Parameters like max attachment size.

Configure System Parameters

7)  Finish wizard
Now finish the wizard and go back to SSIS / SSDT.

Finish

Close

8) Add OLE DB Connection Manager
Add an OLE DB Connection Manager and connect to the server where you configured DatabaseMail.

OLE DB Connection Manager

9) Add Execute SQL Task
Add an Execute SQL Task to the Control Flow or an Event Handler. Edit it and select the new connection manager. In the SQLStatement field we are executing the sp_send_dbmail stored procedure with some parameters to get the, subject, body and from address from the SSIS variables.

1 2 3 4 5 6 7

' Stored Procedure with parameters EXEC msdb.dbo.sp_send_dbmail     @profile_name = 'SSIS Joost Mail Profile',     @recipients = ?,     @subject = ?,     @body = ?,     @body_format = 'HTML' ;

sp_send_dbmail in SSIS

10) Parameters
Go to the Parameter Mapping pane and add the SSIS string variables as parameters. We can't configure the FROM address because we did that already in SSMS (Step 4).

Parameters

11) The result
Now execute the Execute SQL Task and watch your mailbox.


An email with html formatting

If you don't like this solution, check out the Script Task solution or the third party tasks. http://microsoft-ssis.blogspot.ca/2013/08/sending-mail-within-ssis-part-3-execute.html