DTS Package Workflow in Visual Basic

DTS Programming

DTS Programming

DTS Package Workflow in Visual Basic

You create workflow in Data Transformation Services (DTS) packages by assigning all the tasks to steps and defining precedence relationships between the steps. Task objects that are not assigned to steps can be included in the package, but they will not be executed.

Creating DTS Step Objects

Here are the basic steps for adding Step objects in Microsoft® Visual Basic®:

  1. Create a Step object with the New method of the Steps collection of the Package2 object.

  2. Assign a unique step name to the Name property and assign the name of the associated task to the TaskName property.

  3. Set other Step object properties, as appropriate.

    If package event handlers coded in Visual Basic are being used, the ExecuteInMainThread property must be set TRUE. Visual Basic does not support free threading, which DTS uses.

  4. Use the Add method of the Step collection to add the Step object to the collection.
Example

The following code example shows you how to create, include, and assign a task to a Step object:

'Declare the step and the generic and class-specific task.
Dim objTask       As DTS.Task
Dim objStep       As DTS.Step
Dim objDataPump   As DTS.DataPumpTask2
. . .
'Create the step and task, and then link the step to the task.
Set objStep = objPackage.Steps.New
Set objTask = objPackage.Tasks.New("DTSDataPumpTask")
Set objDataPump = objTask.CustomTask
objDataPump.Name = "LowerCaseTask"
With ObjStep
    .Name = "LowerCaseStep"
    .TaskName = objDataPump.Name
    .ExecuteInMainThread = True
End With
objPackage.Steps.Add objStep
Creating Precedence Constraint Objects

Unless otherwise constrained, package steps run in parallel, up to the limit specified by the package MaxConcurrentSteps property. To serialize step execution, create and add PrecedenceConstraint objects to the PrecedenceConstraints collection of the appropriate Step objects.

When one task, the successor task, is not to start execution until some event associated with another task, the predecessor task, occurs, a PrecedenceConstraint object that names the predecessor task is added to the PrecedenceConstraints collection of the successor task. Typically, these events will be step completion, step successful completion, and step failure.

Here are the basic steps for configuring a PrecedenceConstraint object in Visual Basic:

  1. Create the PrecedenceConstraint object with the New method of the PrecedenceConstraints collection of the successor task. Use the name of the predecessor task as the argument to New.

  2. Set the PrecedenceBasis property to indicate whether the constraint is to use step status or step result, and set the Value property to the appropriate step status or result code.

    For more information, see PrecedenceBasis Property and Value Property.

  3. Add the PrecedenceConstraint object to the PrecedenceConstraints collection of the successor task.
Example

In the following example, step TransformData does not run until step ClearTables completes. Step GenerateDoc does not run until TransformData completes successfully. Step SendMail runs only if TransformData fails and begins execution at that point:

'Declare the step and precedence constraint.
Dim objStep        As DTS.Step
Dim objConstraint  As DTS.PrecedenceConstraint
. . .
'TransformData only runs when ClearTables completes.
Set objStep = objPackage.Steps("TransformData")
Set objConstraint = objStep.PrecedenceConstraints.New("ClearTables")
objConstraint.PrecedenceBasis = DTSStepPrecedenceBasis_ExecStatus
objConstraint.Value = DTSStepExecStat_Completed
objStep.precedenceConstraints.Add objConstraint

'GenerateDoc only runs when TransformData is successful.
Set objStep = objPackage.Steps("GenerateDoc")
Set objConstraint = objStep.PrecedenceConstraints.New("TransformData")
objConstraint.PrecedenceBasis = DTSStepPrecedenceBasis_ExecResult
objConstraint.Value = DTSStepExecResult_Success
objStep.precedenceConstraints.Add objConstraint

'SendMail only runs when TransformData fails.
Set objStep = objPackage.Steps("SendMail")
Set objConstraint = objStep.PrecedenceConstraints.New("TransformData")
objConstraint.PrecedenceBasis = DTSStepPrecedenceBasis_ExecResult
objConstraint.Value = DTSStepExecResult_Failure
objStep.precedenceConstraints.Add objConstraint

After the Step and PrecedenceConstraint objects have been added to the Steps and PrecedenceConstraints collections, respectively, the object variables are no longer needed and can be reused for other objects or set to Nothing to release their references.