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:
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:
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
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.
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)
After deleting and re-adding the workflow lines, run the DTS package again and you will see that it is working all correct.
The checkmark in the image is to show "Done... and Done right!
I hope that this article has helped you. Enjoy Development!