Sequence Container - SSIS 2008

Sequence Container
The Sequence container defines a control flow that is a subset of the package control flow. Sequence containers group the package into multiple separate control flows, each containing one or more tasks and containers that run within the overall package control flow.

There are many benefits of using a Sequence container:

·         Disabling groups of tasks to focus package debugging on one subset of the package control flow.

·         Managing properties on multiple tasks in one location by setting properties on a Sequence container instead of on the individual tasks.

·         Providing scope for variables that a group of related tasks and containers use.

Generally the Sequence container can be used in the below scenarios

a.       For transactions :

If we have multiple set of operation to be executed and if any one of the execution is failed then all the executed statement tollback

b.      For Subset of Multiple task

If you want to execute subset of tasks in a group. Eg. I have set of tasks like task1, task2, task3, task4, etc..

Task1 and task2 be executed if some condition is true, else task3 and task4 should be executed

How to Work:

                It is very simple to give sample for sequence container.

                Step a: Drag a sequence container

                Step b: Drag 3 Execute SQL Tasks into the Sequence container

                Step c: Create a OLE DB connection Manager

                Step d: Set valid SQL statement for any two of the SQL Task and for one define invalid SQL Statement.

We will get the below result based on the value set for transactionOption

If the transactionOption is set to ‘Not Supported’ you can see the two tasks are executed successfully and one is failed and overall the sequence container is failed

If the transactionOption is set to ‘Required’ you can see the all three tasks are failed and overall the sequence container is failed