Task Factory Advanced Execute

Advanced Execute Package Task

Task Icon	Task Description
	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 Task is the ability to map variables between the executing package and the child package.

Option	Description
Child Package Location	There are two choices for choosing a child package location: File System - A local file on the executing machine SQL Server - A package stored on a SQL Server
Connection Manager	Only available if SQL Server is selected in the Child Package Location. You may select either an ADO or OLEDB connection manager.
Child Package Path	Depending on the location choice, the package path either points to a local file or the path where it is stored on a SQL Server.
Password	If the child package is password protected you must enter the password here.
Catalog Environment	Users can select environment variables created within SQL Server.
Execution Mode (Catalog execution only)	Users define how the child package executes by selecting one of the following: In Process - (Default) Downloads the package, creates a local ispac project, and executes it locally. Out of Process - (Preferred) Executes the package on the server. This is the most common use because local resources are not used to run the package. You cannot read any parameters after execution. Parameters can only be written to. Run In 32 Bit (Catalog execution with Out of Process mode only) - Executes the child package in 32 bit mode.

Variable Mappings

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

Variable Mapping Option	Description
Read Variable Form Child Package	This mapping direction reads the value of the variable selected in the Child Package Variable and assigns the value to the variable in the Parent Package Variable. The value of the variable is read after the execution of the child package occurs.
Write Variable to Child Package	This mapping direction writes the variable selected in Parent Package Variable and assigns the value to the variable selected in the Child Package Variable. The value of the variable is written before execution of the child package.
Read and Write Variable From Child Package	The mapping direction writes the variable value selected in Parent Package Value and assigns the value to the variable selected in the Child Package Variable before execution occurs and then reads the value of the variable selected in the Child Package Variable and assigns the value to the variable selected in the Parent Package Variable after execution occurs.

Data Type Matching of Variables

The data type of the mapped variables must be the same. For instance if you choose a string variable in the Child Package Variable, only string variables from your package display in the Parent Package Variable drop-down.

What is the difference between In Process and Out of Process modes in the Advanced Execute Package Task?

The following describes each Child Package Location and how variables/parameters are assigned:

File System

• Executes a child package by loading the package from the file system. It is then executed in the process of the parent package. 
• The child package has no knowledge of the project (2012 and higher.) 
• Child packages cannot use project level parameters or connection managers internally.
• Project level parameters can be assigned to child package variables.
• Parent package variables / parameters can be assigned to child package variables.
• Parent package variables can be assigned values from child package parameters and variables after execution.

SQL Server (MSDB, File System)

• Executes a child package by copying the package from SQL Server into the parent package and executing it in the process of the parent package. 
• The child package has no knowledge of the project (2012 and higher.) 
  • Therefore, child packages cannot use project level parameters or connection managers. 
• Project level parameters can be assigned to child package variables. 
• Parent package variables & parameters can be assigned to child package variables. 
• Parent package variables can be assigned values from child package parameters and variables after execution.

Local Catalog Execution

• Copies the package from the Catalog Server into the parent package. 
• The child package executes in the same process as the parent package. 
• The child package has no knowledge of the project (2012 and higher.) 
  • Therefore, child packages cannot use project level parameters or connection managers. Project level parameters can be assigned to child package variables.
• Parent package variables & parameters can be assigned to child package variables. 
• Parent package variables can be assigned values from child package parameters and variables after execution.

Server Catalog Execution (New Feature)

• Will execute a package via server catalog execution (out of process) and wait on the package to finish execution on the server.
• The child package has knowledge of project level variables and connection managers available within the project catalog of the child package being executed.
• The child package has the option of assigning a catalog project environment from the child package project to the execution.
• Child package variables can be assigned project level & package level parameters, package level variables as well as being able to use environment variables from the catalog environments.
• Parent level variables cannot be assigned values from child level parameters and variables after execution because the execution is performed out of process.

Overview of features

This chart is a quick summary of each location, variable assignment, and the execution mode that should be used:

Location	File System	SQL Server	Local Catalog/In Process	Server Catalog/Out Of Process
Parent to child variable access	Read & Write	Read & Write	Read & Write	Write
Child to parent variable access	Read & Write	Read & Write	Read & Write	None
Parent Parameter to child variable assignment	Read	Read	Read	Read
Child package project level parameter access	None	None	Yes via copy	Yes
Child package project level connection manager access	None	None	Yes via copy. No passwords available in connection strings	Yes
Can assign catalog environment variables to child variables	No	No	Yes	Yes
Can use catalog environments during execution	No	No	No	Yes
Execution Process	In process	In process	In process	Out of process

Advanced Execute Process Task

Task Icon	Task Description
	The Advanced Execute Process Task is used to execute commands as if they were being executed from a command window. This allows for the use of basic commands like copy, ping, delete, etc. as well as using any executable you would like to be executed within SSIS. One of the best features is that it allows for multiple commands per instance of the task.

Steps

You are able to add multiple steps to the advanced execute process. A step is a single command that mimics what would be executed from the command line.

Toolbar

The toolbar is used to control the steps for the Advanced Execute Process.

Toolbar button	Description
	Select to add a new Step to the Task.
	Select to delete the currently selected Step in the Steps grid.
	Select to edit the currently selected Step in the Steps grid.
	Select to move the currently selected Step in the Steps grid up in the order of execution.
	Select to move the currently selected Step in the Steps grid down in the order of execution.

Add New Step

Selecting the Add New Step button opens the Add New Process Step window.

Option	Description
Step Name	The name of the current step.
Step Command	The command in which this step executes. The command can use any variables that are part of the package as replacement values in the command.
Timeout (Seconds)	The number of seconds you would like this step/command to run before the process is terminated. A value of 0 means there is no timeout.
Success Exit Code	The exit code of the command which indicates the step was successful. The default success code for the command line is 0.
Window Style	The style in which a command window is shown. The four options are: Hidden, Normal, Minimized, and Maximized.
Standard Output Variable	Choose to store the output to either a variable or command window.
Error Output Variable	Choose to store the error output to either a variable or command window.
Step Description	You can store notes about the step here. When you enter a description here, it shows up when the step is selected in the main UI as shown below.

Reordering the Steps

Use the blue arrows in the toolbar to change the order the steps are executed.