<

DTS Package Workflow Steps not going in Sequence

Published on
10,090 Points
4,090 Views
Last Modified:
Approved
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!
0
Author:AmmarR
Ask questions about what you read
If you have a question about something within an article, you can receive help directly from the article author. Experts Exchange article authors are available to answer questions and further the discussion.
Get 7 days free