Data Transformation Services (DTS) has been around for a while and most database developers are quite familiar with the product. However, while DTS is a powerful tool, it has some limitations, particularly in the area of conditional flow. Although DTS is great at transforming data from one data source to another, it is not easy to send the DTS flow down one logical path and ignore other paths.

When SQL2005 was released, SQL Server Integration Services (SSIS) replaced DTS. SSIS is a fantastic new product, but I struggled to find time to learn the new toolset. This was made worse by the fact that the company I worked for was still running their systems on SQL Server 2000. As a result, it was quicker for me to complete simple tasks using DTS than SSIS. I suspect many people reading this article are experiencing the same issue.

DTS is going to be around for a while, at least until the last SQL2000 instance is shut down. It is important we learn as much as possible about the product, in order to leverage the maximum benefit for the companies we provide services to.

Conditional Flow

I define conditional flow in DTS as the ability to send logical flow down one path of DTS tasks, and ignore others. I used DTS conditional flow at a company I worked at, where Order data was being extracted from a SQL Server table, and put into batches to be read by a legacy Cobol program. You may not have the exact same problem, but the basic logic can be applied in a variety of different situations.

My task was as follows:

1. Count the number of rows that were created since the last extract of Orders;
2. If no rows were found, do not perform any of the remaining tasks;
3. If rows were found, create a batch entry in a batch table. If there were no orders, it was essential that no empty batches were created; hence the previous step;
4. Extract the rows from the SQL Orders table into the processing table on the legacy system;
5. Mark the batch as closed by updating the batch table;
6. The Cobol program ran every 15 minutes, and would only process closed batches this was to avoid incomplete batches from being processed

The reason behind this logic is not important at this time, and I am sure there were many different ways to solve this problem - but I approached it using conditional flow logic in my DTS package. Using conditional flow logic, I was able to complete step 2 to not perform any remaining DTS tasks. This is achieved quite simply by using Workflow Properties in the DTS task object. More on this later - let's get a practical example going.

Let's get an example going!

This simple example will perform the following steps, using six ActiveX script tasks in a DTS package:

1. Display a message saying "I'm here Step 1";
2. Accept input from the user;
3. If the user entered 1, then display a message saying "You entered 1.", followed by a message saying "Here 2";
4. If the user did NOT enter 1, display a message saying "You did not enter 1.", followed by a message saying "Here 3"

To complete this example, you will need a basic understanding of DTS and ActiveX Scripting in DTS. Start by creating a new DTS package, and create a new ActiveX script task with a simple message box (msgbox) saying "I'm here - Step 1":

Next, create a second ActiveX script where you request input from the user, and save the value to a Global Variable. Before you do this, create the Global Variable as follows:

To create the Global Variable, click in the white space in the DTS designer window, select menu option Package / Properties / Global Variables and enter a new string variable called @MyString, with no default value:

Next, create the previously mentioned ActiveX script to accept user input and save it to the new Global Variable:

Join the two ActiveX script objects with a Success workflow:

Create two new ActiveX script task objects as follows, and link them both with a Success workflow to the second task object you have just created.

The first ActiveX script task (call this script task ONE):

The second ActiveX script task (call this script task TWO):

Finally, create two additional ActiveX script tasks. Each of the new tasks should be linked with success workflows to one of the previous two tasks you have just created. The first new script task should have a message box displaying "Here 2" and should follow script task ONE. The second new script task should display "Here 3", and should follow script task TWO. Take a look at the following image, and this final instruction should be clearer.

The final product should look like this:

Run the DTS. You should receive the following prompts after clicking OK on all the message boxes, and entering 1 in the input box.

Notice you received both the last two message boxes, even though you entered 1 in the input box. Let's add some conditional flow logic.

Right-click on the task that displays "You entered 1.", select Workflow / Workflow properties, as shown below:

Next, select the Options tab, tick "Use ActiveX script" at the bottom, and click Properties to the right.

Enter the following text in the Properties dialog and click OK all the way back to the design window:

The constant DTSStepScriptResult_DontExecuteTask is key to this task. If the value in the global variable is not 1, then do not execute this task. All tasks following from this task will not be executed either. This functionality is available on other tasks like Data Transformation, Execute SQL, and Execute Process tasks.

Do the same with the task that displays "You did not enter 1.", with a slight twist, as shown below:

Run the DTS again, and enter 1 in the input box. You will see the following output:

If you look at the Execution Progress after running the package, you will see that two tasks did not run. The first task which did not run was the task displaying "You did not enter 1.", and the second one was the following task displaying "Here 3":

Using this basic logic, applied to your own solutions, you should be able to control the flow of your DTS package.

Conclusion

I hope this article has given you yet another tool for your DTS toolbox. I feel DTS still has a valuable place in the SQL Server world, and I will certainly be using it for several more years before its inevitable, and sad, demise.