I'm using DTS in SQL Server 2000. This is the first time I've used it
extensively.

I have a task which is trying to "manually" roll back a set of steps if any
errors have occurred in a previous step. In the Execution Progress dialog,
the previous step shows up with "Errors occurred (nnn)". Yet,
Step.ExecutionStatus is 4 and Step.ExecutionResult is zero! This leads to
several questions:

1) What is it which is displaying in that Status column, and can I access it
programattically?
2) The transformation which is causing the errors is returning
DTSTransformStat_ExceptionRow. Should it be returning some other value in
order to log the row in error and have the error counted as an error? I
would ideally like to be able to use an On Failure workflow to go to the
step which will handle the errors.
3) I noticed that the corresponding task has RowsInError <> 0. Should I be
looking at the Task instead of the Step?

I'll eventually learn how to use transactions with DTS and this won't be
necessary when that happens. But in the meantime, I could really use some
help with this.

Thanks,
John Saunders

The execution dialog is no doubt driven by the execution events. The error
event would seem most interesting here.

It does sound a bit strange that the result is success. Odes on failure
workflow do the job?
Sometimes the state of a value at execution time, or a stage of execution
can differ from what you see and expect afterwards or just later on.

The step handles timings and status information. This stuff is common to all
tasks. The task wraps the custom task, and it is that which provides the
function specific stuff, such as RowsInError. So use the correct object for
what you need.


--
Darren Green
http://www.sqldts.com
http://www.sqlis.com


"John Saunders" <jsaunders (AT) navimedix (DOT) com> wrote

Darren, thanks for the response. Comments:

"Darren Green" <darren.green (AT) reply-to-newsgroup-sqldts (DOT) com> wrote

I may play with those events soon. Are there some examples you can
recommend? I'm a bit concerned about getting more sophisticated than
necessary - the code should be maintainable, as this process will be used
for some time to come.

No, the failure workflow didn't work at all. That was my first clue. My code
which displayed the Step.ExecutionStatus = 4 and Step.ExecutionResult = 0
was called from a completion workflow (I should try a Success workflow, just
to put the last nail in the coffin).

Thanks, Darren, the distinction makes sense now. Can one reach the Task from
the Step object, or vice-versa?

Also, thinking in terms of encapsulation, can a step determine it's
predecessor(s) in terms of workflows? It would be good if an
error-processing step didn't need to know the name of the preceding step or
task, so that it could be reused.

Thanks for all the help,
John Saunders

"John Saunders" <jsaunders (AT) navimedix (DOT) com> wrote

Ok, duh, Step.TaskName.

John

John Saunders wrote:
<snip>

The only way to tell which constraints apply to a given step, is to loop
through all steps, thyen loop through the precedence constraints, and
look at the StepName property for the PrecedenceConstraint object.


--
Darren
http://www.sqldts.com
http://www.sqlis.com

John Saunders wrote:
<snip>

The only way to tell which constraints apply to a given step, is to loop
through all steps, then loop through the precedence constraints, and
look at the StepName property for the PrecedenceConstraint object.


--
Darren
http://www.sqldts.com
http://www.sqlis.com