Sequence in SSIS package – SSIS

Sequence Container

Sequence container in SSIS helps developers to define the control flow of the package with multiple modules.

It has the capability to group multiple tasks to represent single task.

I would like to make this point very clear. If you are planning to design a SSIS package for the data transformation or database maintenance task then it is time to think about the sequence container in the control flow of your package.

Usually, data transformation or database maintenance starts with checking the availability of the source or cleaning the stage area. So, you can group a set of cleaning task in a sequence container and keep it aside. Now, you can define different sets of task in different meaningful group with sequence container and create the package. Finally, map the success or failure pipeline to the appropriate container.  You may ask. What are the advantages of doing this? Answer is given below.

Advantages:

1. Package maintenance will be easy and it will increase the readability of the package if someone opens the package for the first time, I mean your peers.
2. Disabling groups of tasks within the container while debugging the package.
3. Managing properties on multiple tasks in one location by setting properties on a Sequence container instead of on the individual tasks.
4. Providing scope for variables that a group of related tasks and containers use.
5. Sequence container transaction option provides data integrity solution.
6. The ability during development/debug to right click on the container and execute just that group in isolation to the rest of the package.

Read more about it in MSDN.

Scenario

I need to design a package to accomplish a task.

1. I need to execute a set of predefined maintenance task before actual work starts. It is called startup activity.
2. If there is a problem in the start activity then I have to proceed to another set of tasks to complete the package execution. Perhaps, roll back.
3. If the startup activity is succeeded then it should move on to execute another set of tasks and complete the package execution. Surly, commit.

My package looks like this

Thanks for reading. Please rate this article. If you are looking for a BI professional to help you, then please contact me.

About these ads