SSIS Package Design Concepts – Control Flow and Data Flow

Let’s explore the two fundamental concepts related with package design

  • Control Flow
  • Data Flow

Control flow:

As the name suggest, ‘Control Flow’ means controlling the flow of execution of SSIS package. While designing the ETL package, we need to think how one container can be connected to another, how related tasks can be connected. To connect different components of SSIS, we need to identify whether we need to make tasks sequential or parallel. This whole design depicts the flow of execution and is implemented in ‘Control Flow’ component.

Control flow can consist of multiple tasks such as DFTs, Sequence Container, Execute SQL task and so on. These tasks are linked with the help of ‘Precedence Constraints’ (explained later). Following snapshot shows the location of ‘Control Flow’ in SSIS designer

Control Flow in SSIS Designer

The below screenshot shows an example of how the flow looks like:

Flow of Execution

Note:

  1. The two sequence containers will run in parallel with each other
  2. Within the sequence container, the tasks mentioned will run in sequence. Ex: First sequence container contains 3 tasks – ‘Execute SQL Task’, ‘DFT task’ and ‘For Each Loop Container’. Firstly the ‘Execute SQL Task’ will execute then ‘DFT task’ and then ‘For Each task’
  3. ‘Send Mail Task’ will execute when both the containers execute successfully

Data Flow Task (DFT):

DFT is the heart of SSIS. Most of ETL operations are performed in DFT. As the name suggest, this task is used to transfer the data from source to destination. While transferring the data, operations like merge, merge join, data conversion etc. can be performed. This whole process involves Extracting the data from source, Transformation of data in the form expected in destination and then Load the data into destination which is nothing but ETL. However, the ‘Transformation’ part is optional.

DFT has to be a part of Control flow. To add DFT to control flow, drag the DFT task from SSIS toolbox to designer. Below screenshot shows a DFT in Control flow:

DFT

On double clicking the DFT, Data Flow Tab will open where you can add Source, Destination and any transformation if required. Below screenshot adds one source, transformation and one destination:

DFT

Precedence Constraint:

Now we have different tasks in control flow or DFTs, but the question is how will we create flow between these tasks? For this purpose, Precedence Constraint is used. They connect different tasks\ containers and direct the flow of execution.

Precedence Constraint can be of 3 types:

  • Success – indicated by Green arrow
  • Failure – indicated by Red arrow
  • Completion – indicated by Blue arrow

Below screenshot will show different Precedence Constraint:

Precedence Constraint

How to create Precedence Constraint?

Whenever you drag any task from SSIS toolbox to the designer, you must have noticed a green arrow coming from task. To create a Precedence Constraint, you need to connect this arrow with another task\ container.

On double clicking the constraint following dialogue box will open:

Precedence Constraint

One can perform following things on Precedence Constraint:

  • Write Constraints
  • Write Expressions
  • For multiple constraints you can choose Logical And/ Logical Or
Advertisements

One thought on “SSIS Package Design Concepts – Control Flow and Data Flow

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