DTS Package Workflow Steps not going in Sequence

AID: 3490
  • Status: Published

2580 points

  • ByAmmarR
  • TypeTips/Tricks
  • Posted on2010-07-25 at 23:51:02
Awards
  • 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:
Dts-Package.jpg
  • 24 KB
  • 1 A DTS Package
1 A DTS Package


The Problem

Problem.jpg
  • 72 KB
  • 2 The Problem
2 The Problem

As 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:
Normal-workflow.jpg
  • 50 KB
  • 3 Normal Workflow
3 Normal Workflow

If you double click on a workflow line to view the Properties, you will see:

   source step ---> Precedence ---> Destination step
   Step1            ---> Success         ---> Transfer: Table 1
 
Workflow-Line-1.jpg
  • 47 KB
  • 4 Workflow Line properties
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.
Workflow-Line-Missing.jpg
  • 65 KB
  • 5 Workflow Line not updated
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)
Fix-Workflow-line.jpg
  • 75 KB
  • 6 Fix Workflow line
6 Fix Workflow line

After deleting and re-adding the workflow lines, run the DTS package again and you will see that it is working all correct.
Solved.jpg
  • 60 KB
  • 7 Solved
7 Solved

The checkmark in the image is to show "Done... and Done right!"
I hope that this article has helped you.  Enjoy Development!
    Asked On
    2010-07-25 at 23:51:02ID3490
    Tags

    DTS

    ,

    SQL Server 2000

    Topic

    MS SQL DTS

    Views
    1533

    Comments

    Add your Comment

    Please Sign up or Log in to comment on this article.

    Join Experts Exchange Today

    Gain Access to all our Tech Resources

    Get personalized answers

    Ask unlimited questions

    Access Proven Solutions

    Search 3.2 million solutions

    Read In-Depth How-To Guides

    1000+ articles, demos, & tips

    Watch Step by Step Tutorials

    Learn direct from top tech pros

    And Much More!

    Your complete tech resource

    See Plans and Pricing

    30-day free trial. Register in 60 seconds.

    Loading Advertisement...

    Top DTS Experts

    1. TempDBA

      2,000

      0 points yesterday

      Profile
      Rank: Sage
    2. AmmarR

      330

      10 points yesterday

      Profile
      Rank: Wizard

    Hall Of Fame