Data Transformation Services (DTS) has been a popular tool among database developers for quite some time. However, it has its limitations, especially when it comes to conditional flow. SQL Server Integration Services (SSIS) was introduced in SQL Server 2005 as a replacement for DTS, offering a more powerful and flexible solution. Despite the advantages of SSIS, many developers still find themselves using DTS due to various reasons, such as working with older versions of SQL Server. In this article, we will explore the concept of conditional flow in DTS and how it can be applied to solve real-world problems.
Understanding Conditional Flow
Conditional flow in DTS refers to the ability to direct the logical flow of tasks based on certain conditions. This means that you can choose to execute specific tasks while ignoring others, depending on the outcome of a condition. Let’s consider a practical example to illustrate this concept.
An Example Scenario
Imagine you are working on a project where you need to extract order data from a SQL Server table and process it in batches using a legacy Cobol program. Your task is as follows:
- Count the number of rows created since the last extract of orders.
- If no rows are found, skip the remaining tasks.
- If rows are found, create a batch entry in a batch table.
- Extract the rows from the SQL Orders table into the processing table on the legacy system.
- Mark the batch as closed by updating the batch table.
In this scenario, it is crucial to avoid creating empty batches when there are no orders. To achieve this, we can utilize conditional flow logic in our DTS package.
Implementing Conditional Flow in DTS
Let’s walk through a simple example that demonstrates how to implement conditional flow using six ActiveX script tasks in a DTS package:
- Display a message saying “I’m here Step 1”.
- Accept input from the user.
- If the user enters 1, display a message saying “You entered 1”, followed by a message saying “Here 2”.
- If the user does NOT enter 1, display a message saying “You did not enter 1”, followed by a message saying “Here 3”.
To follow along, you will need a basic understanding of DTS and ActiveX Scripting in DTS. Here are the steps to create the DTS package:
- Create a new DTS package and add an ActiveX script task to display the message “I’m here – Step 1”.
- Create a Global Variable called @MyString to store user input.
- Add an ActiveX script task to accept user input and save it to the Global Variable.
- Create two additional ActiveX script tasks to display the messages “Here 2” and “Here 3”.
- Link the tasks together using Success workflows.
Once you have completed these steps, run the DTS package. You will see the messages displayed based on the user input. However, at this point, both “Here 2” and “Here 3” messages will be displayed regardless of the input.
Adding Conditional Flow Logic
To add conditional flow logic, right-click on the task that displays “You entered 1” and select Workflow Properties. In the Options tab, select “Use ActiveX script” and click Properties. Enter the following script:
if DTSGlobalVariables("MyString").Value <> "1" then DTS.TaskResult = DTSStepScriptResult_DontExecuteTask end if
This script ensures that the task will only be executed if the value in the Global Variable is equal to 1. If the condition is not met, the task will not be executed, and any tasks linked to it will also be skipped.
Repeat the same process for the task that displays “You did not enter 1”, but modify the script to check for a different condition:
if DTSGlobalVariables("MyString").Value = "1" then DTS.TaskResult = DTSStepScriptResult_DontExecuteTask end if
Now, when you run the DTS package and enter 1 as the user input, only the “Here 2” message will be displayed. If you enter any other value, only the “Here 3” message will be displayed. This demonstrates how conditional flow logic can control the execution of tasks in a DTS package.
Conclusion
Conditional flow is a powerful feature in DTS that allows you to control the logical flow of tasks based on specific conditions. While SQL Server Integration Services (SSIS) has replaced DTS, it is important to understand and utilize the capabilities of DTS, especially when working with older versions of SQL Server. By mastering conditional flow logic, you can enhance the functionality and efficiency of your DTS packages. So, don’t overlook the potential of DTS and continue to leverage its benefits until its eventual retirement.