Updated March 15, 2023
Introduction to SSIS Sequence Container
The package control of SSIS is used to execute the various task for designing the workflow and execution. Therefore, the container plays an important role in the design of workflow. The containers in the SSIS toolbox are for loop container, sequence container, and foreach loop container. To execute a finite number of tasks for the loop container used. The foreach loop is similar to the for loop and is used to execute a collection of a finite number of tasks. The Sequence container is used to group the task, and the user can divide the control flow into several logic units using the sequence container. In this article, we can look into sequence containers in brief.
What is an SSIS Sequence container?
The SSIS package has a sequence container as its subset. It behaves as a unit control point for the task that will be defined and executed in the container. The user can define the variable inside the sequence container under the range of tasks. It behaves the patterned relationship of parent-child with the primary tasks. The user can edit the behavior of a parent sequence container, which is navigated to the inbuilt child task. The sequence container provides maximum flexibility to control the task in the container.
SSIS sequence container configuration
The integration services have the sequence container, which divides the package in control flow into a minimum group of containers and tasks to manage as a unit. The sequence container enables the user to manage the entire package in the control flow instead of individual containers and tasks. A package with multiple tasks is used to group the corresponding tasks into sequence containers.
There are multiple types of configurations made in sequence containers.
The sequence task ST1 should be executed first but holds multiple tasks. This group of tasks should be executed before other tasks are in the package. Then Sequence task ST2 should be initiated after the task of ST1. Though ST1 is prone to failure or success, ST2 is executed only after the process of ST1. The Sequence task ST3 has a standard maintenance plan followed after the progress of ST2. Therefore, the ST3 should be executed only after the successful completion of ST2.
In another case, the ST1 should be initially executed. Then sequence task ST2 should be initiated under logical conditions. It can be dependent on the package variables. Then the Sequence task ST3 with the maintenance plan should be executed after ST1 and ST2. Finally, ST3 should execute after the successful completion of ST2.
Sequence Containers Transactions
The package works with the transaction to link the actions of the database to execute the task by maintaining the integrity of the data. Every Microsoft integration container has For loop, foreach loop, and sequence containers to encapsulate every task by transactions. The three options for configuring the transactions are Required, Supported, and NotSupported.
The Required option denotes the container executed at the transaction time unless initiated by the parent container. If the transaction is already present, then the container associates with the executed transaction. For example, go for the required option if the package is not organized to support the transaction with a sequence container. This is because the sequence container has its transaction, and if the package is customized to use the required option, then the sequence container can associate with the package transaction.
The supported option is denoted to the container, which is not started but can link with any transaction of the parent container. For example, if the package with multiple SQL tasks initiates any transaction, the supported option uses all the tasks. The database updates are done by Execute SQL, where the task is pushed back in case of any problem. On the other hand, if the package doesn’t support any transaction, then the scheduled multiple tasks are not bound by any functions. Therefore, no database makes it update except the one that failed and pushed back to the original position.
NotSupported option of the container doesn’t associate with any existing transactions. It is initiated by the parent container, which is not dependent on the child contained, which is customized to the notsupported option. For example, if any package is customized to initiate a transaction and for loop, the container uses this option where no task present in the for loop can be rolled back if they fail.
SSIS sequence container example
The container has the transaction option where the user can configure all the properties according to the requirement in the server data tool, or it can also be configured programmatically.
In an alternate case, the ST1 should be executed before other tasks, and this sequence container should be comprised of multiple tasks to retrieve the data from mixed data sources. The execution of ST2 should be followed by ST1 and can be used to add or update the dimension tables along with other intermediate values. The container has data of sub-sequence containers to manage the dimension of the fact table and intermediate tables. Then execute the ST3 after completion of ST2, which might be comprised of SSAS task to build the cubes. This container also holds sub-sequence data to ensure whether the partition of the current month is present or not. If it holds the partition, there is nothing to be done; if not, the user can create the present month partition using script tasks. ST4 is used only after ST3, and it holds a maintenance plan to take the cube backup, compress and retrieve the backup of datamart. Finally, the ST5 can be used as a recovery plan. If the previous one fails, then data can be restored from the cube. But everything is based on the complexity of the business rules.
Conclusion
Hence it is a brief note on Sequence containers in the SSIS package. Unlike other containers, it doesn’t follow one specific rule; it can be configured with a set of properties and implemented for multiple processes in the design and data workflow.
Recommended Articles
This is a guide to SSIS Sequence Container. Here we discuss the multiple configurations made in sequence containers, and we can look into sequence containers briefly. You may also look at the following articles to learn more –
