Let’s explore the two fundamental concepts related with package design
- Control Flow
- Data 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
The below screenshot shows an example of how the flow looks like:
- The two sequence containers will run in parallel with each other
- 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’
- ‘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:
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:
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:
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:
One can perform following things on Precedence Constraint:
- Write Constraints
- Write Expressions
- For multiple constraints you can choose Logical And/ Logical Or