In simple words, Precedence Constraints are useful to perform the data transportation in a sequential manager based on certain business requirements.
you can design the required workflow in your SSIS package by using Precedence Constraint Editor which is a user interface with simple configurable options (Figure 1).
As you can see in above screenshot (Figure 1), Precedence Constraints can configure by two options such as Constraint Options and By Logical Operations. We will checkout both of them in detail in the below explanation.
Workflow by Constraint OptionsThere are four ways to configure this section such as Constraint, Expression, Expression and Constraint, Expression or Constraint.
1. ConstraintIf business requirement is to run the next executable only based on Constraint then value can be configured such as Success, Failure and Completion. (Figure 1)
Success: Current executable has to run successfully then only next level executable will start running.
Failure: It will allow to start next execution only when current executable got failed, this constraint maybe useful to capture the log for failure execution.
Completion: In this constraint, next executable has to start running whether previously linked executable was completed successfully or not.
2. ExpressionIf next executable need to run based on an Expression value for true evaluation then you can select this option. This option is very rare in terms of business requirement but you can define running the next executable based on certain Expression evaluation as well. If you choose this option then you can see in screenshot (Figure 2) that value section has been disabled while a text box has been enabled to write an expression.
3. Expression and Constraint
4. Expression or Constraint
Workflow by Logical Operations
1. Workflow by 'Logical And'
2. Workflow by 'Logical Or'
There is a business scenario for college (Figure 4), in which one project table will be filled for a particular department which received maximum number of projects submitted for the current day. The basic workflow is defined as below -
1. Check the @RowCount (EST 1) of project table if it has been already filled for a department which received maximum number of projects for the current day.
2. There are two conditions as below -
Condition 1 - If @RowCount is equal to zero then find the find department name from all projects submission which has maximum number of projects submitted for the day (EST 2).
Condition 2 - If @RowCount is greater than zero then truncate the project table (EST 3) and find the department name from all the project submission which has second largest number of project submission for the day.
3. Fill the data into project table using Data Flow Task (DFT 1). Here Data Flow Task will execute on the basis of input value either from the EST 2 or EST 4 (Logical OR, dotted arrows).
4. There are two Send Mail Tasks for the success and failure activity of Data Flow Task (DFT 1) respectively. Here executable Send Mail Task with green arrow will run only for successful run of Data Flow Task (DFT 1) and executable Send Mail Task with red arrow will run only if Data Flow Task (DFT 1) got failed due to some reason.
5. Start backup utility after the completion of Data Flow Task (DFT 1). Here is the example of Completion constraint which means backup utility has to run for logging purpose even though Data Flow Task (DFT 1) executed successfully or not.