Bandwidth Monitor
SQLDTS.comSQLIS.comSQLIS Wiki
Introduction to Workflow
By Darren Green
Version 7.0/2000
Level Beginner

This article will give you a quick overview of the three types of workflow precedence constraints available and also highlight a couple of common problems associated with workflow. The most basic feature workflow gives you is the ability to control in which order your tasks will be executed. If all tasks have workflow constraints applied you can serialise an entire package. The different types of workflow constraints filter on the task result. This can be used to control the execution path in the case of failure or success of a specific task. A task must complete for the workflow to be evaluated.

On Success

OnSuccess Task A must complete successfully before Task B will execute. If Task A fails, Task B will not execute. This is the most commonly used constraint and ensures all tasks execute successfully, and in the correct order.
 

On Completion

OnSuccess Task A must complete before Task B will execute. Task B will execute regardless of the result of Task A, but Task A must complete. Also known as the unconditional constraint, it is used to help serialise tasks, but allows the execution to continue regardless of whether the preceding task succeeds or fails. Whilst it can be useful to ignore failures it can also be confusing since the overall package result will be failure. In a well designed package it is normal to check for common problems such as missing files before allowing the task to execute.
 

On Failure

OnSuccess Task A must fail before Task B will execute. If Task A succeeds, Task B will not execute. You can use this task to provide error reporting or custom logging of failed tasks. For example a SendMail task can be linked On Failure from a DataPump task, so that you receive an email telling you about the failure of the DataPump.
 

Multiple Constraint Behaviour

287_OnFailure_AND.gif One of the most common problems surrounding workflow is the use of multiple constraints, and a misunderstanding of how they interact. In the example shown, for Task C to execute Task A and Task B must complete, and the result must be failure for both tasks. If only Task A were to fail then Task C would not be executed. There is a logical AND relationship between the multiple constraints on Task C, so both must be satisfied. This logical AND behaviour holds for all types of constraints, not just the On Failure constraint as illustrated.

Fail package on first error

The Fail Package on first error option (FailOnError property, Package object) will force a package to terminate if any step raises an error. This option is set in the Error Handling section on the Logging tab of the Package Properties dialog. Whilst not strictly a workflow related property, it can have an effect on the behaviour of your workflow. When this option is on any On Completion or On Failure constraints are redundant as they will never be followed in the case of the related task failing.

Similar behaviour to Fail Package on first error can be set on an individual step basis using the Fail package on step failure option (FailPackageOnError property, Step2 object). This causes the same behaviour described above, but only where the task that fails is controlled by a step with this option set.

For more advanced workflow solutions see the Workflow section of the site.