Integration Services Containers

 

Applies To: SQL Server 2016

Containers are objects in SQL Server Integration Services that provide structure to packages and services to tasks. They support repeating control flows in packages, and they group tasks and containers into meaningful units of work. Containers can include other containers in addition to tasks.

Packages use containers for the following purposes:

  • Repeat tasks for each element in a collection, such as files in a folder, schemas, or SQL Server Management Objects (SMO) objects. For example, a package can run Transact-SQL statements that reside in multiple files.

  • Repeat tasks until a specified expression evaluates to false. For example, a package can send a different e-mail message seven times, one time for every day of the week.

  • Group tasks and containers that must succeed or fail as a unit. For example, a package can group tasks that delete and add rows in a database table, and then commit or roll back all the tasks when one fails.

Integration Services provides four types of containers for building packages. The following table lists the container types.

ContainerDescription
Foreach Loop ContainerRuns a control flow repeatedly by using an enumerator.
For Loop ContainerRuns a control flow repeatedly by testing a condition.
Sequence ContainerGroups tasks and containers into control flows that are subsets of the package control flow.
Task Host ContainerProvides services to a single task.

Packages and events handlers are also types of containers. For information see Integration Services (SSIS) Packages and Integration Services (SSIS) Event Handlers.

Summary of Container Properties

All container types have a set of properties in common. If you create packages using the graphical tools that Integration Services provides, the Properties window lists the following properties for the Foreach Loop, For Loop, and Sequence containers. The task host container properties are configured as part of configuring the task that the task host encapsulates. You set the Task Host properties when you configure the task.

PropertyDescription
DelayValidationA Boolean value that indicates whether validation of the container is delayed until run time. The default value for this property is False.

For more information, see DelayValidation.
DescriptionThe container description. The property contains a string, but may be blank.

For more information, see Description.
DisableA Boolean value that indicates whether the container runs. The default value for this property is False.

For more information, see Disable.
DisableEventHandlersA Boolean value that indicates whether the event handlers associated with the container run. The default value for this property is False.
FailPackageOnFailureA Boolean value that specifies whether the package fails if an error occurs in the container. The default value for this property is False.

For more information, see FailPackageOnFailure.
FailParentOnFailureA Boolean value that specifies whether the parent container fails if an error occurs in the container. The default value for this property is False.

For more information, see FailParentOnFailure.
ForcedExecutionValueIf ForceExecutionValue is set to True, the object that contains the optional execution value for the container. The default value of this property is 0.

For more information, see ForcedExecutionValue.
ForcedExecutionValueTypeThe data type of ForcedExecutionValue. The default value of this property is Int32.
ForceExecutionResultA value that specifies the forced result of running the package or container. The values are None, Success, Failure, and Completion. The default value for this property is None.

For more information, see ForceExecutionResult.
ForceExecutionValueA Boolean value that specifies whether the optional execution value of the container should be forced to contain a particular value. The default value of this property is False.

For more information, see ForceExecutionValue.
IDThe container GUID, which is assigned when the package is created. This property is read-only.

 ID.
IsolationLevelThe isolation level of the container transaction. The values are Unspecified, Chaos, ReadUncommitted, ReadCommitted, RepeatableRead, Serializable, and Snapshot. The default value of this property is Serializable. For more information, see IsolationLevel.
LocaleIDA Microsoft Win32 locale. The default value of this property is the locale of the operating system on the local computer.

For more information, see LocaleID.
LoggingModeA value that specifies the logging behavior of the container. The values are Disabled, Enabled, and UseParentSetting. The default value of this property is UseParentSetting. For more information, see DTSLoggingMode.
MaximumErrorCountThe maximum number of errors that can occur before a container stops running. The default value of this property is 1.

For more information, see MaximumErrorCount.
NameThe name of the container.

For more information, see Name.
TransactionOptionThe transactional participation of the container. The values are NotSupported, Supported, Required. The default value of this property is Supported. For more information, see DTSTransactionOption.

To learn about all the properties that are available to Foreach Loop, For Loop, Sequence, and Task Host containers when configure them programmatically, see the following Integration Services API topics:

  • T:Microsoft.SqlServer.Dts.Runtime.ForEachLoop

  • T:Microsoft.SqlServer.Dts.Runtime.ForLoop

  • T:Microsoft.SqlServer.Dts.Runtime.Sequence

  • T:Microsoft.SqlServer.Dts.Runtime.TaskHost

Containers include control flows that consist of executables and precedence constraints, and may use event handlers, and variables. The task host container is an exception: because the task host container encapsulates a single task, it does not use precedence constraints.

Executables

Executables refers to the container-level tasks and any containers within the container. An executable can be one of the tasks and containers that Integration Services provides or a custom task. For more information, see Integration Services Tasks.

Precedence Constraints

Precedence constraints link containers and tasks within the same parent container into an ordered control flow. For more information, see Precedence Constraints.

Event Handlers

Event handlers at the container level respond to events raised by the container or the objects it includes. For more information, see Integration Services (SSIS) Event Handlers.

Variables

Variables that are used in containers include the container-level system variables that Integration Services provides and the user-defined variables that the container uses. For more information, see Integration Services (SSIS) Variables.

When you set a breakpoint on a container and the break condition is Break when the container recevies the OnVariableValueChanged event, define the variable in the container scope.

Control Flow

Community Additions

ADD
Show: