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.
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.
STEP 2 : Failure
- Double click on SSIS precedence constraint (green color Arrow) and select Failure option.
- Now, Execution control will not pass from Execute SQL task to Data Flow Tasks if Execute SQL task is failed.
STEP 3 : Completion
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.
- Double click on green color Arrow and choose Completion value.
- Data Flow Tasks will execute on Completion of Execute SQL Task, where Completion refers to either Success or Failure.
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.