MS SQL Server DTS - Workflow and Precedence for Transform Data Tasks?

Author

Workflow and Precedence for Transform Data Tasks?

mariestock123@yahoo.com

2005-09-27, 3:23 am

Hi,

I'm trying to create a simple DTS package in SQL Server 2000 using
Enterprise Manager ....

I've 2 DB connections and multiple Transform Data Tasks (i.e. gray
arrows) between them.

I was wondering how I can control the order of the Transform Data Tasks
(i.e. to have Transform Data Task 1 perform before Transform Data Task
2, and Transform Data Task 2 perform before 3, etc) ?

I tried selecting 2 Transform Data Task arrows and then selecting
Workflow/On Success from the menu, but I get the error message "Fewer
than two items have been selected. Select two or more items to
definite precedence." in return.

Can I do so by creating 2 DB connection icons for each Transform Data
Task, thereby having more than 1 icon for each same underlying DB, and
then a Workflow/Precedence green arrow between the destination DB
connection icon of the last Transform Data Task and the source DB
connection icon of the following Transform Data Tasks? E.g.:

Icon 1 for DB 1 ->
Gray arrow for Transform Data Task 1 ->
Icon 1 for DB 2 ->
Green Arrow for Workflow/Precedence ->

Icon 2 for DB 1 ->
Gray arrow for Transform Data Task 2 ->
Icon 2 for DB 2 ->
Green Arrow for Workflow/Precedence ->

Icon 3 for DB 1 ->
Gray arrow for Transform Data Task 3 ->
Icon 3 for DB 2

?

Thanks in advance,
Marie

Allan Mitchell

2005-09-27, 9:24 am

Here is a good article giving an intro to workflow

Introduction to Workflow
(http://www.sqldts.com/default.aspx?287)

The idea you have about ordering the execution of tasks is essentally
correct.

You would not select the arrows to perform workflow if I get your
description correctly rather you would select the destination connection
in the first datapump task and then the source connection in the second
datapump task. If you do this and then right click on the source
connection you will get a workflow context menu. Doing it the other way
you will not.

Allan

"mariestock123@yahoo.com" <mariestock123@yahoo.com> wrote in message
news:1127803908.846975.147130@g14g2000cwa.googlegroups.com:

> Hi,
>
> I'm trying to create a simple DTS package in SQL Server 2000 using
> Enterprise Manager ....
>
> I've 2 DB connections and multiple Transform Data Tasks (i.e. gray
> arrows) between them.
>
>
> I was wondering how I can control the order of the Transform Data Tasks
> (i.e. to have Transform Data Task 1 perform before Transform Data Task
> 2, and Transform Data Task 2 perform before 3, etc) ?
>
> I tried selecting 2 Transform Data Task arrows and then selecting
> Workflow/On Success from the menu, but I get the error message "Fewer
> than two items have been selected. Select two or more items to
> definite precedence." in return.
>
>
> Can I do so by creating 2 DB connection icons for each Transform Data
> Task, thereby having more than 1 icon for each same underlying DB, and
> then a Workflow/Precedence green arrow between the destination DB
> connection icon of the last Transform Data Task and the source DB
> connection icon of the following Transform Data Tasks? E.g.:
>
> Icon 1 for DB 1 ->
> Gray arrow for Transform Data Task 1 ->
> Icon 1 for DB 2 ->
> Green Arrow for Workflow/Precedence ->
>
> Icon 2 for DB 1 ->
> Gray arrow for Transform Data Task 2 ->
> Icon 2 for DB 2 ->
> Green Arrow for Workflow/Precedence ->
>
> Icon 3 for DB 1 ->
> Gray arrow for Transform Data Task 3 ->
> Icon 3 for DB 2
>
> ?
>
>
> Thanks in advance,
> Marie

mariestock123@yahoo.com

2005-09-27, 8:24 pm

Thank you, Allan.

I followed your recommendation but when I selected 2 data connections
and right clicked, the Workflow item is grayed out. I tried selecting
Workflow/On Success from the menu and I get the error message "Defining
precedences between the selected items is not valid".

Do you know what I did wrong?

It is almost as if at least one end of the Workflow arrow needs to be a
Task (as opposed to both being Connections).

The reason I'm using multiple Transform Data Tasks is because I need to
copy to 1 table first, then another table in the same DB, and I can't
seem to create Multiple Destination Tables in the same Transform Data
Task. (Or should I have been able to do this?)

Do you have any other recommendations on how to do this?

E.g. is there any way to create some kind of Dummy Task that I can use
as a place holder between the destination connection in the first
datapump task and then the source connection in the second datapump
task?

Thanks again,
Marie

Allan Mitchell wrote:[color=darkred
]
> Here is a good article giving an intro to workflow
>
> Introduction to Workflow
> (http://www.sqldts.com/default.aspx?287)
>
> The idea you have about ordering the execution of tasks is essentally
> correct.
>
>
> You would not select the arrows to perform workflow if I get your
> description correctly rather you would select the destination connection
> in the first datapump task and then the source connection in the second
> datapump task. If you do this and then right click on the source
> connection you will get a workflow context menu. Doing it the other way
> you will not.
>
>
> Allan
>
>
>
>
>
> "mariestock123@yahoo.com" <mariestock123@yahoo.com> wrote in message
> news:1127803908.846975.147130@g14g2000cwa.googlegroups.com:
>

Allan Mitchell

2005-09-28, 3:24 am

OK So the way I see your package set-up is this

Source1 --> Dest 1 -- ON SUCCESS --> Source 2 --> Dest 2

To enable the workflow between "Dest 1" and "Source 2" you would do as I
previously described.

Highlight Dest 1 | Hold Down CTL | Highlight Source 2 | Right Click on
Source 2 | Workflow.

Allan

"mariestock123@yahoo.com" <mariestock123@yahoo.com> wrote in message
news:1127858723.383797.61390@g44g2000cwa.googlegroups.com:
[color=darkred]
> Thank you, Allan.
>
> I followed your recommendation but when I selected 2 data connections
> and right clicked, the Workflow item is grayed out. I tried selecting
> Workflow/On Success from the menu and I get the error message "Defining
> precedences between the selected items is not valid".
>
> Do you know what I did wrong?
>
>
> It is almost as if at least one end of the Workflow arrow needs to be a
> Task (as opposed to both being Connections).
>
>
> The reason I'm using multiple Transform Data Tasks is because I need to
> copy to 1 table first, then another table in the same DB, and I can't
> seem to create Multiple Destination Tables in the same Transform Data
> Task. (Or should I have been able to do this?)
>
> Do you have any other recommendations on how to do this?
>
> E.g. is there any way to create some kind of Dummy Task that I can use
> as a place holder between the destination connection in the first
> datapump task and then the source connection in the second datapump
> task?
>
>
> Thanks again,
> Marie
>
>
> Allan Mitchell wrote:

Sponsored Links