Creating DTS Package Workflow and Tasks
New Information - SQL Server 2000 SP3.
Workflow in Data Transformation Services (DTS) packages is implemented by creating steps, which are the units of functionality, and precedence relationships between steps, which determine the sequencing of the steps. Tasks are the components the steps use to perform their functions.
Creating the Step Object
You need to create a Step2 object for each operation the package is to perform. For each step, you create a Task object of the appropriate type. The Task object performs the operation for the step.
To create a Step2 object, use the New method of the Steps collection of the Package2 object. Set the TaskName property of the Step object to the name of the associated task. Other properties you can use are:
- ActiveXScript, FunctionName, and ScriptLanguage. These properties specify the Microsoft® ActiveX® script to run before the task.
Security Note Scripts can be the source of security vulnerabilities; they can invoke system functions without user knowledge or intervention and may contain security credentials in plain text. Review the script for security issues before use. For more information, see Security and Scripting.
- CommitSuccess, JoinTransactionIfPresent, RollbackFailure. These properties determine whether the step uses the package transactions.
- ExecuteInMainThread. This property runs the step in the package main thread rather than in a worker thread.
- FailPackageOnError. This property fails the package if the step fails.
Then, Add the Step2 object to the Steps collection of the package.
Creating the Precedence Constraint Object
When a package is executed, DTS attempts to execute steps in parallel up to the limit established by the MaxConcurrentSteps property of the Package2 object. However, you can order the steps by using precedence constraints. A Precedence Constraint object inhibits the step with which it is associated from starting execution until an event by another named step occurs. As a result, the step only begins execution when all of its precedence constraints have been satisfied.
To create the PrecedenceConstraint object, use the New method of the PrecedenceConstraints collection of the Step object. Set its StepName property to the name of the preceding task and set the PrecedenceBasis and Value properties to specify the type of event. Then, Add the PrecedenceConstraint to the PrecedenceConstraints collection of the associated Step object.
For more information about configuring the Step and PrecedenceConstraint objects and the PrecedenceConstraints collection, see DTS Package Workflow in Visual Basic.
Creating the Task Object
To implement a DTS task, you need a generic Task object and a task object specific to the task class being created (for example, a DataDrivenQueryTask2 object or a BulkInsertTask object). To create both of these, use the New method of the Tasks collection of the Package2 object.
Configure the properties of these objects as appropriate for the processing you want to perform. While the elements of the generic Task object manipulate information generic to all tasks, those of the class-specific task object manipulate information unique to the class. The CustomTask property of the Task object returns a reference to the class-specific task object. The properties of the class-specific task object also can be referenced through the Properties collection of the (generic) Task object.
Add each Task object to the Tasks collection of the Package2 object.
For more information about the task classes supplied with Microsoft SQL Server™ 2000, see Task Objects.
For more information about configuring the Task object and the class-specific task objects, see DTS Tasks in Visual Basic.