Tuesday, June 15, 2010

SSIS Containers

Integration Services Containers
SSIS Containers are controls (objects) that provide structure to SSIS packages. Containers 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.

Types of SSIS Container
SSIS provides four types of containers. I'll explain these containers with example in my following Posts.
The following table lists the container types:

Container TypeContainer DescriptionPurpose of SSIS Container
Foreach Loop ContainerThis container runs a Control Flow repeatedly using an enumerator.To repeat tasks for each element in a collection, for example retrieve files from a folder, running T-SQL statements that reside in multiple files, or running a command for multiple objects.
For Loop ContainerThis container runs a Control Flow repeatedly by checking  conditional expression (same as For Loop in programming language).To 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.
Sequence ContainerGroups tasks as well as containers into Control Flows that are subsets of the package Control Flow.This container 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.
Task Host ContainerProvides services to a single task.The task Host container encapsulates a single task. But this task is not configured separately in SSIS Designer. It is configured when you set the properties of the task it encapsulates.

Containers are fundamental to the operation of transactions, checkpoints and event handlers. Each container has some common properties that affect the usage of these features. Understanding these properties and what they do helps a lot in the developing SSIS packages.

PropertyDescription
DelayValidationA Boolean value that indicates whether validation of the container is delayed until run time
DisableA Boolean value that indicates whether the container runs
DisableEventHandlersA Boolean value that indicates whether the event handlers associated with the container run
FailPackageOnFailureA Boolean value that specifies whether the package fails if an error occurs in the container.
FailParentOnErrorA Boolean value that specifies whether the parent container fails if an error occurs in the container.
IsolationLevelThe isolation level of the container transaction. The values are Unspecified, Chaos, ReadUncommitted, ReadCommitted, RepeatableRead, Serializable, and Snapshot.
MaximumErrorCountThe maximum number of errors that can occur before a container stops running.
TransactionOptionThe transactional participation of the container. The values are NotSupported, Supported, Required.

reference: bol

1 comment:

Here are few FREE resources you may find helpful.