If you are a beginner for SSIS, you should know the difference between "Control Flow" and "Data Flow".
First we start with Control Flow
CONTROL FLOW:
As the name suggest, "Control Flow" means controlling the flow of execution of SSIS package. Before designing the SSIS package, you have to know the flow of containers/tasks.
For ex: you want to design SSIS package with following conditions .
i. Data has to load to SQL server
ii. Before loading the data , already present data in the table should be delete.
To design above package, first we have to create "Execute SQL task" then connect it to " Data flow task" by using "precedence containers" to load the data. i will discuss later about "Precedence Constraints".
Control flow consists of many components like... Execute SQL task, For Each loop Container,Data flow task.. and so on. We need to understand whether we have to connect sequential or parallel.
Below image explains parallel and sequential connections.
In the above package, "Bulk Insert Task" and "Data Flow Task" execute serially . "Sequence Container" and "For each loop Container" execute simultaneously.
Data Flow task:
Data flow task is heart of SSIS. Major ETL tasks performed in "Data Flow task" only. As the name suggested, this task is used to transfer the data from Source to destination. While transferring the data, we can use transformations like... conditional split, row sampling, derived column.. etc. The entire process is called... Extracting data from Source , Transforming the data into it desired and Load the data into Destination, which is called ETL . In this "Transformation" is optional.
In the above package
Source: OLEDB Source ( From where we are extracting the data)
Transformation: Performing some operations to get required format
Destination: Flat File (Loading the data into Destination).
Precedence Constraints:
Precedence constraints used in "Control Flow" task. In Control Flow , if you are executing sequentially, for connecting each task with other we use precedence constraints . These are 3 types
1. Success (Color: Green) : if task1 executes successfully then go to task2
2. Failure (Color: Red): if task1 fails then go to task3
3. Completion (Color: Blue): if task1 completes (irrespective of success or failure) go to task4.
Below image will help you to understand better.
Creation of precedence Constraint:
When we add any task in control flow designer from tool box, you have noticed one green arrow coming from that task. To create a precedence constraint , you need to connect this arrow with another task/container. If you want to change the constraint from success (Green) to Failure(Red)/ Completion(Blue), you need to double click on that arrow, then following dialogue box will open.
You can also perform following in precedence constraints:
First we start with Control Flow
CONTROL FLOW:
As the name suggest, "Control Flow" means controlling the flow of execution of SSIS package. Before designing the SSIS package, you have to know the flow of containers/tasks.
For ex: you want to design SSIS package with following conditions .
i. Data has to load to SQL server
ii. Before loading the data , already present data in the table should be delete.
To design above package, first we have to create "Execute SQL task" then connect it to " Data flow task" by using "precedence containers" to load the data. i will discuss later about "Precedence Constraints".
Control flow consists of many components like... Execute SQL task, For Each loop Container,Data flow task.. and so on. We need to understand whether we have to connect sequential or parallel.
Below image explains parallel and sequential connections.
In the above package, "Bulk Insert Task" and "Data Flow Task" execute serially . "Sequence Container" and "For each loop Container" execute simultaneously.
Data Flow task:
Data flow task is heart of SSIS. Major ETL tasks performed in "Data Flow task" only. As the name suggested, this task is used to transfer the data from Source to destination. While transferring the data, we can use transformations like... conditional split, row sampling, derived column.. etc. The entire process is called... Extracting data from Source , Transforming the data into it desired and Load the data into Destination, which is called ETL . In this "Transformation" is optional.
Source: OLEDB Source ( From where we are extracting the data)
Transformation: Performing some operations to get required format
Destination: Flat File (Loading the data into Destination).
Precedence Constraints:
Precedence constraints used in "Control Flow" task. In Control Flow , if you are executing sequentially, for connecting each task with other we use precedence constraints . These are 3 types
1. Success (Color: Green) : if task1 executes successfully then go to task2
2. Failure (Color: Red): if task1 fails then go to task3
3. Completion (Color: Blue): if task1 completes (irrespective of success or failure) go to task4.
Below image will help you to understand better.
When we add any task in control flow designer from tool box, you have noticed one green arrow coming from that task. To create a precedence constraint , you need to connect this arrow with another task/container. If you want to change the constraint from success (Green) to Failure(Red)/ Completion(Blue), you need to double click on that arrow, then following dialogue box will open.
You can also perform following in precedence constraints:
- Writing Constraints
- Writing Expressions
- For Multiple constraints, you can select "Logical AND " or "Logical OR".
No comments:
Post a Comment