Task Factory Advanced Execute

Advanced Execute Package Task

Advanced Execute Package Task

Task IconTask Description
Task Factory Advanced Execute Package Task IconThe 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.

Task Factory Advanced Execute Package Task

OptionDescription
Child Package LocationThere 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 ManagerOnly available if SQL Server is selected in the Child Package Location. You may select either an ADO or OLEDB connection manager.
Child Package PathDepending on the location choice, the package path either points to a local file or the path where it is stored on a SQL Server.
PasswordIf the child package is password protected you must enter the password here.
Catalog EnvironmentUsers 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.

  • 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:


File SystemSQL ServerLocal Catalog/In ProcessServer Catalog/Out Of Process
Parent to child variable accessRead & WriteRead & WriteRead & WriteWrite
Child to parent variable accessRead & WriteRead & WriteRead & WriteNone
Parent Parameter to child variable assignmentReadReadReadRead
Child package project level parameter accessNoneNoneYes via copyYes
Child package project level connection manager accessNoneNoneYes via copy. No passwords available in connection stringsYes
Can assign catalog environment variables to child variablesNoNoYesYes
Can use catalog environments during executionNoNoNoYes
Execution ProcessIn processIn processIn processOut of process
Advanced Execute Process Task

Advanced Execute Process Task

Task IconTask Description
Task Factory Advanced Execute Process Task IconThe 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, del, etc. as well as using any executable you would like to be executed within SSIS. One of the best features is it allows for multiple commands per instance of the task.

Task Factory Advanced Execute Process 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 buttonDescription
Task Factory New Step button Select to add a new Step to the Task.
Task Factory Delete Step button Select to delete the currently selected Step in the Steps grid.
Task Factory Edit Step button Select to edit the currently selected Step in the Steps grid.
Task Factory Move Step Up button Select to move the currently selected Step in the Steps grid up in the order of execution.
Task Factory Move Step Down button 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.

Task Factory Add New Process Step

Option Description
Step NameThe name of the current step.
Step CommandThe 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 CodeThe exit code of the command which indicates the step was successful. The default success code for the command line is 0.
Window StyleThe style in which a command window is shown. The four options are: Hidden, Normal, Minimized, and Maximized.
Standard Output VariableChoose to store the output to either a variable or command window.
Error Output VariableChoose to store the error output to either a variable or command window.
Step DescriptionYou 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.