May 29, 2012 at 7:47 am
I am working on a custom error tracking technique for an ETL package.
If there are rows redirected (error output) it does not seem to raise the "OnError" event handler.
Is it possible?
thanks
May 29, 2012 at 8:01 am
Don't quote me but I believe if you are redirecting rows to an error output then it needs to be handled in the dataflow. The OnError is triggered by an error, the redirect rows prevents the error from firing.
May 29, 2012 at 1:12 pm
I believe Daniel is correct, in this scenarion there ISN'T an error, its a problem that is managed. So you can have one or the other but not both.
CEWII
May 29, 2012 at 1:17 pm
Ok thanks
What I am trying to do is use flagging instead of row redirection.
For example :
Source data has 10 rows
There is an error in row 3 (say violates null constraint on destination column)
I want to flag row 3 in the source table as "Error" but continue processing the other legit rows.
The issue now is that the container fails when it hits row 3 and will not continue to process.
I want to insert the error information about row 3 into a logging table. If I use "Ignore failure"
on the error output then I will not trigger the OnError event handler.
any tips?
May 29, 2012 at 1:23 pm
If you redirect error rows the Data-Flow task should not fail. did I misunderstand?
CEWII
May 29, 2012 at 1:27 pm
yeah I am trying get away from row redirection.
basically I want to leave the rows that have errors in the source table,
and just flag them with 'Error' in some flag field.
I'd like the process to not fail on the first error, rather raise the OnError
event to put some information about the bad rows only into a table, and
continue processing the rest of the source data.
May 29, 2012 at 1:35 pm
Listen the redirect shouldn't fail the data-flow, thats its purpose.. You can use the redirect on error to either write the records into a holding location or depending on the locking of the source tables you can use a OLEDB Command in the redirect to write the error back into the source.
I don't see using OnError for this, I would expect that to fail the DFT at the first error.
CEWII
May 30, 2012 at 7:05 am
Whether you use the OnError, or redirect in the data flow, your net effect is the same.
Forgive me because I don't usually handle errors this way but if you want to use the OnError, I believe you must put some dataflow transforms in the OnError event handler to channel the rows into another table or do what you would like with them.
In either case the end result is the same. It is 6 of one and half dozen of another.
May 30, 2012 at 7:13 am
that's correct.
the OnError event handler is a stored proc which inserts rows into a row-level error table.
it also inserts system variables (package name, user name) into a container specific table.
the issue is that once an error row is encountered, the data flow fails and processing stops.
the ideal process would consist of :
1) begin pulling data from source
2) when error is encountered, fire event handler (sproc to do some inserts into error detail and summary table)
3) continue processing remainder of rows
May 30, 2012 at 11:57 am
I guess my question boils down to this :
Is there a way to continue processing rows after encountering the first
error in a dataflow task without using row re-direction?
thanks
May 31, 2012 at 6:33 am
There might be, but I don't know why you don't want to use the redirection. If marking them as an error is important, then redirect the error rows into another table, and then join to that table at the end to mark the rows in the original table as in error.
This will allow your other rows to continue processing.
May 31, 2012 at 2:49 pm
You could change the MaximumErrorCount number of either the package or the task so that the package doesn't fail when it encounters an error.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply