As a SQL Server consultant, Data Transformation Services (DTS) is most likely your tool of choice for Extracting, Transforming, and Loading (ETL) data. While you may have found the data manipulation tasks in a DTS fast, easy-to-use, and flexible, you may be disappointed in the limited workflow capabilities of DTS. DTS workflow—i.e., moving from one task to another contingent on the results of a previous step—is limited to simple ideas like: continue executing the package if a task succeeds or quit the package if a task fails. For some tasks success or failure may be all you need, but what about conditions like executing one out of four steps depending on the value of a stored procedure call to the database? Controlling what is executed depending on something more than a previous task’s success or failure can be described as conditional workflow.
Exporting the DTS package to VB and adding code to execute certain steps if special conditions exist in your database is certainly one option to develop this conditional workflow. There are, however, alternative options that require less heavy-lifting. In fact, implementing more sophisticated workflow does not require software development skills. Using some basic DTS Tasks, you will be surprised by what you can accomplish.
Typically a DTS package will roll-up or massage data from various data sources and copy the data to a database for reporting or other Business Intelligence (BI) activities. This example focuses on techniques for changing workflow during the execution of a DTS package. Therefore, the sample performs some atypical actions like displaying Message boxes to give you a sense of where changes to the workflow are occurring. A summary of the DTS Package example actions appear below:
- The package uses three global variables for passing and storing values sent to and retrieved from another DTS package.
- Depending on the global variable returned from the alternate DTS Package, the package branches and executes a "Success" workflow or a "Failure" workflow.
- After executing the "Success" workflow, the package executes a "Dynamic Properties" step, which changes the workflow property of the last step from "Failure" to "Success".
- Following the Dynamic Properties step, the package copies data and then executes a VBScript.
We will discuss the package in detail in the sections below, starting with the first two steps in the package.
Using Global Variables
Global Variables are a good mechanism for capturing the execution result of another DTS package. Using Global Variables in conjunction with the VBScript Tasks enables decision making based on the results. The example uses the following global variables:
- LocationOfDTS - The location of another DTS Package the example executes programmatically
- SendValue - A value the example passes to another DTS package
- ReceiveValue - A value which stores the returned value of the DTS package executed programmatically (The ReceiveValue will be used later in the DTS package.)
Global Variables are used in conjunction with VBScript Tasks. Using VBScript Tasks along with the DTS COM+ API, the example modifies global variables and passes global variable results into and out of another DTS package. The sample code appears below:
Dim DTSPackage 'As DTS.Package
Set DTSPackage = CreateObject("DTS.Package")
DTSPackage.LoadFromStorageFile DTSGlobalVariables("LocationOfDTS").Value, ""
DTSPackage.FailOnError = True
DTSPackage.GlobalVariables("SendValue") = DTSGlobalVariables("SendValue").Value
MsgBox "You will execute " & CStr(DTSPackage.GlobalVariables("ReceiveValue"))
Observe that the DTSPackage.GlobalVariables ("variable name in package") property allows read and write access to the global variables in a programmatically executed DTS package. The sample instantiates the DTS.Package Object and opens the DTS package. Then, using the GlobalVariables property, the example copies the "SendValue" from the executing DTS package to the "SendValue" of the package the example executes programmatically.
You can extend the example in various ways to meet your needs. VBScript supports COM +/ActiveX interface objects. Almost anything with a COM+/ActiveX interface supported in VBScript can be used to fulfill requirements in your own solutions. Some examples are provided below:
- ADO would allow you to invoke SQL statements on a myriad of database vendors.
- A custom VB ActiveX/COM program can be constructed to perform other actions.
- Software vendors sometimes provide a COM+/ActiveX interface with their software. Invoking a software vendor’s COM+/ActiveX interface would allow you to perform actions in the vendor’s software.
- A giant case statement would allow you to execute many different items depending on the value of a global variable.
Other workflow conditions can be controlled in a DTS package using Global Variables and VBScript Tasks. Among the more straightforward conditions are the Execution Results of a VBScript Task.
DTS Package execution results
DTS Execution results fall into two categories: Success or Failure. Workflow can be configured to proceed on a Success path or a Failure path. In Figure A the "Run 1 or the other" VBScript Task is configured with a Success and a Failure workflow path.
Returning the appropriate Task Execution Result constant from the VBScript task will execute the appropriate branch in the workflow. A code example appears below.
If DTSGlobalVariables("ReceiveValue").Value = "ReceiveSuccess" Then
Main = DTSTaskExecResult_Success
Main = DTSTaskExecResult_Failure
In the code above, notice that the example uses the Global Variables populated by the DTS Package programmatically invoked by the "Copy Global Variables" VBScript Task executed earlier in the workflow. So far all examples have required some kind of coding. Now you will see how changing workflow using Dynamic Properties requires little or no coding.
Dynamic property changes to workflow
Dynamic Properties allow you to change almost everything in your DTS package during package execution. Dynamic Properties dealing with workflow appear in Figure B:
One obvious omission in the figure above is a more descriptive name for the steps in the DTS package. To determine the name used in the steps section of the dynamic properties, reference the Tasks section and find the Task with the appropriate description as shown in Figure C.
Changing the Value property of the appropriate Precedence Constraint to "0" (Success) or "1" (Failure) will change the workflow. The example changes the workflow using a Constant value, but you can change the workflow condition using anything appropriate for a Dynamic Property. A list of possibilities appears in Figure D.
Easy as one, two, three
Changing workflow during the execution of a DTS Package need not be complicated. You don’t have to be a software developer to build Conditional Workflow into your DTS Package. A variety of requirements can be satisfied with a few lines of VBScript, DTS Global Variables, and the Dynamic Properties Task.