Precedence Constraints - SQL Server 2008

In the previous example, you applied a precedence constraint that used an expression to determine which e-mail should be sent. As we’ve discussed, precedence constraints are used to define package workflow, determining if and when specific tasks are executed and in what order. Tasks (including containers) can be connected to other tasks or to other containers.

The following example reviews how to configure precedent constraints within the Control Flow design surface. You will add tasks but not configure their properties, since we’re focusing on how precedence constraints work.

  1. In the SSIS Designer, using a new package, drag and drop a File System task onto the Control Flow design surface.
  2. Drag and drop a Send Mail task onto the Control Flow design surface.
  3. To make sure that the File System task executes first, click the File System task and drag the green arrow onto the Send Email task. The arrow defaults to green, meaning that the Send Email task will execute only if the File System task executes successfully.
  4. Double-click the green arrow to open the Precedence Constraint Editor. The Evaluation operation drop-down list designates whether or not a constraint is evaluated, an expression, an expression and a constraint, or an expression or a constraint. When Constraint is selected, the Exception result drop-down lists whether the next task executes on success, failure, or completion of the preceding task.
    • If Expression is selected as an evaluation operation, the Expression dialog box becomes available. Expression evaluation was demonstrated in the previous exercise, when you evaluated the value of the JobCandidateCount user variable.
    • The Multiple Constraints section applies to multiple constraints referencing the same task.
  5. Keep the Evaluation operation selection of Constraint. Change the Execution result to Failure and click OK. Notice that the green arrow has turned to red. The Send Mail task will now be fired only if the File System task fails.

All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd Protection Status

SQL Server 2008 Topics