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

The SetupPath contains all of the core code for this example. First we start by defining some constants, these are all step names that will be required within the code. Using constants in this way makes for more readily reusable code as all changes can be made in one place.

As the comments indicate we have two paths, a True path and a False path. We need to know the step names for the start and end of each path, and the final step on which we will simulate the OR constraint.

' 218 (SetupPath)
Option Explicit

' /* Change these constants to suit your package */

' Name of the global variabled used to decide the path
' This example requires a boolean
Const DTSPath_GlobalVariable_Name = "ExecuteTruePath"

' Name of the first Step in the "True" Path
Const DTSPath_True_First_Step = "DTSStep_DTSActiveScriptTask_1"
' Name of the last Step in the "True" Path
Const DTSPath_True_Last_Step = "DTSStep_DTSActiveScriptTask_3"

' Name of the first Step in the "False" Path
Const DTSPath_False_First_Step = "DTSStep_DTSActiveScriptTask_4"
' Name of the last Step in the "False" Path
Const DTSPath_False_Last_Step = "DTSStep_DTSActiveScriptTask_6"

' Name of OR Step
Const DTSPath_OR_Step = "DTSStep_DTSActiveScriptTask_8"


Function Main()

	Dim oPkg, oConstraints, oConstraint

	' Get reference to the package
	Set oPkg = DTSGlobalVariables.Parent

	' Diable the workflow path we are not using
	oPkg.Steps(DTSPath_True_First_Step).DisableStep = _
		Not DTSGlobalVariables(DTSPath_GlobalVariable_Name).Value
	oPkg.Steps(DTSPath_False_First_Step).DisableStep  = _
		DTSGlobalVariables(DTSPath_GlobalVariable_Name).Value

	' Get reference to the PrecedenceConstraints collection, 
	' for which we need to simulate the OR
	Set oConstraints = oPkg.Steps(DTSPath_OR_Step).PrecedenceConstraints


	If DTSGlobalVariables("ExecuteTruePath").Value Then
		' Set our True path OR
		Set oConstraint = GetConstraint(oConstraints, DTSPath_True_Last_Step)
		oConstraint.PrecedenceBasis = DTSStepPrecedenceBasis_ExecResult
		oConstraint.Value = DTSStepExecResult_Success

		Set oConstraint = GetConstraint(oConstraints, DTSPath_False_Last_Step)
		oConstraint.PrecedenceBasis = DTSStepPrecedenceBasis_ExecStatus
		oConstraint.Value = DTSStepExecStat_Inactive Or DTSStepExecStat_Waiting
		oPkg.Steps(DTSPath_False_Last_Step).ExecutionStatus = _
			DTSStepExecStat_Inactive
	Else
		' Set our False path OR
		Set oConstraint = GetConstraint(oConstraints, DTSPath_False_Last_Step)
		oConstraint.PrecedenceBasis = DTSStepPrecedenceBasis_ExecResult
		oConstraint.Value = DTSStepExecResult_Success

		Set oConstraint = GetConstraint(oConstraints, DTSPath_True_Last_Step)
		oConstraint.PrecedenceBasis = DTSStepPrecedenceBasis_ExecStatus
		oConstraint.Value = DTSStepExecStat_Inactive Or DTSStepExecStat_Waiting
		oPkg.Steps(DTSPath_True_Last_Step).ExecutionStatus = _
			DTSStepExecStat_Inactive
	End If

	Set oPkg = Nothing
	Set oConstraints = Nothing
	Set oConstraint = Nothing

	Main = DTSTaskExecResult_Success
End Function

Function GetConstraint(oConstraints, sStepName)
	' Get Constraint by Source Step Name
	Dim oConstraint
	For Each oConstraint In oConstraints
		If oConstraint.StepName = sStepName Then
			Set GetConstraint = oConstraint
			Exit For
		End If
	Next
End Function 
Page 1 | Page 2 | Page 3