Documentation forTask Factory

Task Factory Advanced Execute

 Task Factory users running version 2020.1.4 or older (released prior to May 27, 2020): There's an important Task Factory update. Please visit here for more details.

Advanced Execute Package Task

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

Task Factory Advanced Execute Package Task

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
Task Factory Advanced Execute Process Task Icon 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.

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 button Description
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 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.