Home > SSIS > Sequence in SSIS package – SSIS

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.

Advertisements
Categories: SSIS Tags:
  1. Chin Chin Su
    June 10, 2011 at 4:37 am

    Nice article. Yep, I have used the sequence containers in my BI design. It is a great tool.

  2. June 10, 2011 at 8:47 am

    Thanks Chin Chin Su!

  3. Raghu
    February 26, 2015 at 7:40 pm

    Very Nice Information.. now i understood very clearly about Sequence Container.

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: