Bandwidth Monitor
SQLDTS.comSQLIS.comSQLIS Wiki
Multiple Paths in Workflow
By Darren Green
Page 1 | Page 2 | Page 3

The key to this method is the way in which the standard workflow constraints are manipulated dynamically by the ActiveX script in the SetupPath task. The first point to note is that a workflow constraint can use either the result (ExecutionResult Property) or status (ExecutionStatus Property) of the source step to determine its outcome. This is set via the PrecedenceBasis property of the precedence constraint (PrecedenceConstraint Object).

If we chose the True path, the constraint between the last step in that path (C) and the final step (End) is setup just like a normal on success constraint. The precedence basis is on the result and that result must indicate success, DTSStepExecResult_Success. For the second constraint, leading from the False path, we need to ensure satisfaction of the constraint even though the step will not execute. For this we set the precedence basis to be on the status, and the status must indicate that the step has not run. The obvious choice would be to detect for a status of DTSStepExecStat_Inactive, however the internal DTS workflow engine does not always cascade the status to all downstream steps. To ensure we do not run into this we will also accept waiting status, DTSStepExecStat_Waiting. Notice the logical Or between the two accepted values.

If we choose the False path, the reverse is implemented. The main If statement in SetupPath tests our global variable to decides between the two paths, and sets these properties accordingly.

The final statement within each block of the If statement is used to ensure the current status value for the last step in the inactive path is correct (DTSStepExecStat_Inactive). Without this we may read the status value from a previous execution of the package.

A fully working sample package is available for download, which should illustrate this method better than any description.

Page 1 | Page 2 | Page 3