sql server dts: What I would do is to have two separate objects, with different workflow paths to them: one would lead to the code/object if the prior task completed successfully, and the other would fire off the code/object you wish to run if it fails. Once the code after a failure has completed, if you need to tie back in to the flow of the tasks you can just use another workflow on success arrow to get there. Does that help at all

Regards

Looking at the documentation for this phase, I was under the impression one
could place code in this phase to handle errors that occur during the last
batch insert. To quote SQL Books Online:

"You can call this data pump phase on success or failure of a batch or rows,
as defined by the value specified in Insert batch size in the Options tab of
the Transform Data task. Setting a batch size for a Data Driven Query task
or parallel data pump task can only be done programmatically; if you want to
write an On batch complete function for either of those tasks, you must do
so programmatically."

But once inside this area how does one query the status of the last batch
insert to determine whether there was a success or failure? I'd like to
determine whether there was a failure, react, and continue the next batch.

I don't understand exactly how this would help. Upon leaving then returning
to a task, wouldn't the context be lost, the batch restarting at row 0 --
are you suggesting storing a global variable for the last batch and updating
the FirstRow property of the prior task before returning?

I appreciate your suggestions

I was also just looking at the capabilities of TransformFailure -- as some
of what I need to do can be accomplished there. The lack of context
information available in these error handling events is astonishing. SQL
Books Online actually suggests that to determine what went wrong in a
TransformFailure, one should manually code a type conversion and
corresponding If/Else for every column in the table being loaded to
determine which column failed the transform.

At this rate the error handling code will be twice the size of that needed
for the actual load.



[quoted text, click to view]
paths to them: one would lead to the code/object if the prior task completed
successfully, and the other would fire off the code/object you wish to run
if it fails. Once the code after a failure has completed, if you need to
tie back in to the flow of the tasks you can just use another workflow on
success arrow to get there. Does that help at all?
[quoted text, click to view]