<

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x

DTS Package Workflow Steps not going in Sequence

Published on
10,027 Points
4,027 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
0 Comments

Featured Post

IT Pros Agree: AI and Machine Learning Key

We’d all like to think our company’s data is well protected, but when you ask IT professionals they admit the data probably is not as safe as it could be.

Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Other articles by this author

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month