Home > SSIS Best Practices > SSIS #100 – An SSIS package without Sequence Container?

SSIS #100 – An SSIS package without Sequence Container?

I recently needed to modify an SSIS package to add a few new columns to a table. But I turned out that I had more than just adding columns to do.

I did three things prior to adding the columns:

1. I added three Sequence Containers as part of the Control Flow, and make them run in parallel.

2. I moved three staging tables into the schema that is designated as staging area.

3. I renamed the three staging tables to be consistent in naming.

In this post, I’ll focus on the Sequence Container part.

An SSIS package without Sequence Container

When you open the Toolbox, and take a closer look at the first three items in the Control Flow choices, you will see that they are all Containers of some sort. By the way, the Control Flow Items are not sorted alphabetically.

Why would Microsoft developers place these three items to try to catch our attentions first? I can only guess. My guess is that these three items must be the most commonly used Control Flow items. I cannot say for other SSIS developers, but, for me, this is true. Or at least one the Container is true, that is, Sequence Container. 

 image

The SSIS package that I need to modify looks like this. No Sequence Container.

image

Question 1 – Must each task be executed in the specified order?

With all the Precedence Constraints, I can see that each task must be executed in the order. Do we really need to execute each task in the specified order?

The answer is NO after some poking around each task.

Question 2 – Where are the “touching” points?

With the skeleton-like control sequence, I cannot really tell the “touching” points, i.e. when does the package “touch” the staging and when does it “touch” the production table(s).

In my ETL work, it’s important for me to distinguish the tasks that only “touch” the staging area, and the tasks that “touch” the target.

An SSIS package with 3 Sequence Containers in parallel

This is the final version of the package after I added 3 Sequence Containers.

1. Each Sequence Container has one specific task to do, that is, to populate one staging table.

2. These three tasks do not need to run in the sequence, instead, they can run in parallel.

3. Running tasks in parallel can increase the performance of the package, and also gives clear visual clues on each task’s duty.

4. The last step is to combine the three staging tables and populate a target table.

image

Give the Sequence Container a try in your own SSIS package design next time. Your co-workers will come to appreciate it.

Categories: SSIS Best Practices
  1. No comments yet.
  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: