It's used to grouping logically related tasks together which makes it simple to divide the control flow in a package into groups of tasks and containers that we can manage as a single unit.
Purpose of the Sequence Container
Grouping tasks so that we can disable a part of the package which is no longer in use.
Narrowing the scope of a variable to a container.
Managing the property of multiple tasks into one by setting the property of container.
Ensure that all tasks in the container must be executed before the next task executes.
Creating a transactions across a series of data related tasks, but not on the entire package.
Creating the event handler for a single container. We can send an email if something fails inside the container.
Transactions
TransactionOption
Required indicates that the container starts a transaction, unless one is already started by its parent container. If a transaction already exists, the container joins the transaction. For example, if a package that is not configured to support transactions includes a Sequence container that uses the Required option, the Sequence container would start its own transaction. If the package were configured to use the Required option, the Sequence container would join the package transaction.
Supported indicates that the container does not start a transaction, but joins any transaction started by its parent container. For example, if a package with four Execute SQL tasks starts a transaction and all four tasks use the Supported option, the database updates performed by the Execute SQL tasks are rolled back if any task fails. If the package does not start a transaction, the four Execute SQL tasks are not bound by a transaction, and no database updates except the ones performed by the failed task are rolled back.
NotSupported indicates that the container does not start a transaction or join an existing transaction. A transaction started by a parent container does not affect child containers that have been configured to not support transactions. For example, if a package is configured to start a transaction and a For Loop container in the package uses the NotSupported option, none of the tasks in the For Loop can roll back if they fail.
A. I have a Sequence Container with 3 Execute SQL Task (1,2,3). Each of them is inserting one numeric value into a SQL Server table. As long as the Execute SQL Tasks are not connected, they are performing a task in PARALLELE. This means that each task will execute in a different order after each package run.
Frist task is inserting a string value into a numeric column. As long as the tasks are not connected, the first one failed and Task 2 and 3 succeeded. If you have TansactionOption: Supported or Notsupported you will get the same result. If you set the TansactionOption to Required, Sequence container will fail and none of these tasks will run, that means no data will be inserted.
If the Execute SQL Tasks are connected they run in sequence. If connected, the sequence is interrupted when the error occurs. In this case we had an incorrect value in the second task, just the first task has ran, tasks after the first one failed.
(TransactionOption: Supported, NotSuppoted)
TransactionOption: Required
Task 2 is containing an incorrect value, all the transaction in the sequence container have been rolled back.
