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.

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
John Miceli

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.



"John Miceli" <jmiceli (AT) wrberkley (DOT) com> wrote

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?

I'd like to take a look at this approach to see if there is anything I can
borrow from it. My e-address is matt d0t edwards a_t earthlink d0t net.

Thanks!

"John Miceli" <jmiceli (AT) wrberkley (DOT) com> wrote

correctly. Each individual piece of code must be checked because the
@@error will reset with any processing of code.
depending on the error and tailor error messages. We don't really need this
level of granularity, but hey, I didn't write it; I just inherited it. We
also log each step into a logfile with a standardized form for debugging
purposes. We have a stored procedure that does the error reporting
processing to the log file. If this code would be of assistance I can
forward it to you.

Well, I've just discovered a way to capture exception information while
continuing row processing in TransformError (this workaround accomplishes
what most people want DTSTransformStat_SkipRowInfo to do):

Begin by setting the MaximumErrorCount to 0. The task should fail once any
kind of error is raised. In your TransformError phase, after handling the
error, add:

Dim oTask, i
Set oTask = DTSGlobalVariables.Parent.Tasks("DTSTask_DTSDataPu mpTask_16")
i = oTask.Properties("MaximumErrorCount").Value
oTask.Properties("MaximumErrorCount").Value = i + 1
Set oTask = Nothing

Finally, return:

TransFailureMain = DTSTransformStat_ExceptionRow

You will get:

1. Detailed context info in the exception log file configured for this task.
2. The ability to continue processing, skipping the bad row.
3. Safety, knowing that errors for which you haven't defined error handling
will still cause the task to fail.

Moderator: Feel free to add this to your FAQ.



"John Miceli" <jmiceli (AT) wrberkley (DOT) com> wrote

correctly. Each individual piece of code must be checked because the
@@error will reset with any processing of code.
depending on the error and tailor error messages. We don't really need this
level of granularity, but hey, I didn't write it; I just inherited it. We
also log each step into a logfile with a standardized form for debugging
purposes. We have a stored procedure that does the error reporting
processing to the log file. If this code would be of assistance I can
forward it to you.

Well sorry, but this just doesn't work (my initial tests only worked because
I had forgotten to reset MaximumErrorCount to 0 before starting the test).

Apparently none of the properties of a Task object are modifed or modifiable
while the task is executing. Properties like MaximumErrorCount and
RowsInError, which could otherwise be used to overcome what is looking like
a _gaping_ exception logging gap are simply unavailable to help the user
programmatically control task execution.

Any suggestions regarding ways to capture detailed error information
(without jumping through the hoops suggested in SQLBooksOnline) with
SkipRowInfo are greatly appreciated.



"Isak Dinesen" <no5pam (AT) hotmail (DOT) com> wrote

Please repost a working email. I've tried the one you posted a couple of levels ago, but it didn't appear to work. I have an email with code samples ready for you.

Regards,
John Miceli