DTS Package Workflow Steps not going in Sequence

Published on
9,979 Points
Last Modified:
Community Pick
Sometimes when I create a simple DTS package with only few steps describing how tasks should be executed and in what sequence, I get surprised to see that the steps are not executed in the sequence I defined.  Some steps start parallel and other steps which are supposed to run at the end are running at the beginning.

The image below shows a task that includes transfer of three tables:
1 A DTS Package

The Problem

2 The ProblemAs you can see in the image, Step 5 and Execute SQL are executing before Transfer: Table 2 and Transfer: Table 3.

But in the sequence,  Transfer: Table 1, Transfer: Table 2, and Transfer: Table 3 should all run simultaneously and then Step 5 and Execute SQL should run after the Transfer steps.

Now why is that???

I believe that it is a bug in DTS.   Sometimes just recreating the whole package from scratch seems to fix the problem. But often, even after re-developing, I'm stuck with the same issue.  This article demonstrates the problem and how to avoid it.

Actually the solution is very simple; I think it is a bug in the DTS workflow steps.  Anyway, let's start.  
Let's redevelop this DTS package and I will show you where the problem starts.  In this test, we'll start by transferring a single table.

    1:  (Step1) Add an ActiveX Script
    2:  Add a connection to source
    3:  Add a connection to destination
    4:  (Step5) Add an ActiveX Script
    5:  Add an (Execute SQL task)
    6:  Click on Source Conn and press ctrl, click on Destination Conn and add a Transform Data Task (Transfer Table 1)
    7:  Add a success workflow between ActiveX (Step1) to Source conn
    8:  Add a Success workflow between Destination conn to ActiveX (Step5)
    9:  Add a complete workflow between ActiveX (Step5) and (Execute SQL task)

Run the Package, and you will see every thing is running just fine and in the right Sequence.  It should look like this now:
3 Normal WorkflowIf you double click on a workflow line to view the Properties, you will see:

   source step ---> Precedence ---> Destination step
   Step1            ---> Success         ---> Transfer: Table 1
 4 Workflow Line properties...as expected.

Now try adding:
    Transform Data Task (Transfer Table 2)
    Transform Data Task (Transfer Table 3)

...and run the Package again.  This time, you will see the  original problem.  This is because the Workflow Lines don't get updated automatically.
5 Workflow Line not updated

The Fix

To fix this problem, all you need to do is delete the workflow line and add it again, this way it will update itself automatically.  In the case, you will need to delete both of the lines between
    Step1(ActiveX) and Source conn
...and the line between...
    Destination Conn and Step 5(ActiveX)
6 Fix Workflow lineAfter deleting and re-adding the workflow lines, run the DTS package again and you will see that it is working all correct.
7 SolvedThe checkmark in the image is to show "Done... and Done right!"
I hope that this article has helped you.  Enjoy Development!

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Join & Write a Comment

Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
Other articles by this author

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month