SSIS Precedence constraint – Success, Failure and Completion

0
1427

Aim :-  Today, we are going to watch and learn a very interesting thing in SSIS i.e. Precedence Constraints. They are like Soldiers of a country, very important and essential yet no limelight. Moreover, they don’t feel bad for no recognition of their work.

Description :-  In SSIS package, Precedence constraints are the arrows in a Control flow which is responsible for connecting the tasks together. Additionally they also manage the order in which the various tasks present in the control flow will execute. One very awesome characteristic of Precedence constraint is that it determines the condition to pass the execution control to which task. The default precedent constraint is Success (Out of Success, Failure, Completion options) and is represented with green color Arrow between the tasks.

NOTE Precedence constraint in Control flow is known as Service path in Data Flow pane.

Precedence Constraints in SSIS

Let us take an example where Execute SQL task and Data flow tasks are connected with SSIS precedence constraints.

Precedence Constraint in SSIS
Precedence Constraint in SSIS

STEP 1 : Success (By default)

Execution control will pass further from Execute SQL task to the 2 Data flow tasks only if Execute SQL task is successfully completed otherwise next tasks will not execute. This is because by default precedence constraint is Success.

Example of Precedence constraint Success option (by default) in SSIS
Example of Precedence constraint Success option (by default) in SSIS

STEP 2Failure

SSIS Precedence constraint - Failure option
SSIS Precedence constraint – Failure option
  1. Double click on SSIS precedence constraint (green color Arrow) and select Failure option.
  2. Now, Execution control will not pass from Execute SQL task to Data Flow Tasks if Execute SQL task is failed.
Example of Precedence constraint Failure option in SSIS
Example of Precedence constraint Failure option in SSIS

STEP 3Completion

It is a world of Customized requirements and so is our SSIS tool. Sometimes, Client regardless of the scenario – whether Success or Failure, wanted to pass the Execution control to pass to further tasks (Data Flow tasks in our example). In simple words, we need to pass Execution control to next task whether the previous task linked with precedence constraints got successfully executed or failed.

SSIS Precedence constraint - Completion option
SSIS Precedence constraint – Completion option
  1. Double click on green color Arrow and choose Completion value.
  2. Data Flow Tasks will execute on Completion of Execute SQL Task, where Completion refers to either Success or Failure.
Example of Precedence constraint Completion option in SSIS
Example of Precedence constraint Completion option in SSIS

Finally, we reached to an end of this interesting post on “SSIS Precedence constraint” and we learned How to configure Precedence constraint in SSIS to Success, Failure and Completion. You can try numerous other things apart from the one listed in this post. We hope you must have enjoyed this article and will appreciate your feedback. Post as comments below if you have any questions or difficulties in regard to this post.

 

 

VN:F [1.9.22_1171]
Your Feedback hels to Improve
Rating: 8.6/10 (7 votes cast)
VN:F [1.9.22_1171]
Rating: +5 (from 5 votes)
SSIS Precedence constraint – Success, Failure and Completion, 8.6 out of 10 based on 7 ratings