November 13, 2008 at 7:55 am
Correct me if I'm wrong.
I'm doing a small test to choose which way to handle errors is better.
In my table I have a column RUN_NUM (int) NOT NULL.
I force SSIS package to attempt to write NULL into this column.
And here is an interesting thing I discovered.
If in my OLE DB Destination Error Output is set to "Fail package"
and I have OnError Event Handler enabled I can capture the exact
Error Description:
There was an error with input column "RUN_NUM" (3186) ... The value violated the integrity constraints for the column
And I can write this error into my ErrorLog table.
Not perfect. It does not say something like "Can not insert NULL into RUN_NUM.." but it's good enough. You know which column is causing the error.
The problem with this approach is that you don't know which exactly record failed.
Scenario 2.
In the same OLE DB Destination Error Output is set to "Redirect row".
I pass ErrorCode and ErrorColumn columns to a script and then write to an ErrorLog table.
But!
I do not get the same quality in my Error Handling.
Now my OnError Event Handler is being ignored. It does not get executed.
I suspect because when Error Output = Redirect row it has priority and it ignors any other OnError Event Handlers.
In the Error Output pipeline only ErrorCode and ErrorColumn columns are available now.
Error Code Error Column
------------------------------------
-1071607683 3168
and when you translate ErrorCode
Row.ErrorDesc = ComponentMetaData.GetErrorDescription(Row.ErrorCode).Trim()
you get this Error Description:
The data value violates integrity constraints.
That's all. It does not specify the name of the column which is in trouble.
As far as I understand it's impossible to translate ErrorColumn(3186) into a readable real column name.
I tried
Row.ErrColumn = ComponentMetaData.GetErrorDescription(Row.ErrorColumn).Trim()
'Row.ErrColumn = ComponentMetaData.InputCollection(0).InputColumnCollection(Row.ErrorColumn).Name
always errors. I read some discussions about ErrorColumn and it looks like none of the developers have been able to translate ErrorColumn.
So if you have 45 columns imagine eye balling through the whole record to guess which column could cause the error.
Not fun...
My goal is to record errors and still to be able to write "good" records into the destination table. But in this case ErrorCode is not very helpful when it comes to troubleshooting.
Are there any other solutions to this problem?
I would appreciate any feedback on this.
October 23, 2009 at 7:43 am
I have the same problem, i dont know how obtain the column name when i try to insert the data.
October 23, 2009 at 11:18 am
We have two destinations usually: one with Data access mode - fast load. It is configured to redirect error output to the second, that saves rows in the same table in 'slow mode'. The second destination can also be set up to redirect failing rows to a file or a table - we have script compontent that builds XML representation of the row (using reflection). Anyway, you could output these failing rows to a text file, then you would see which of them are failing.
So, you divide fast destination into batches. If there is a row in the batch that violates the constraint, the whole batch is rolled back and redirected to the 'slow' destination, which performs inserts one by one. Then if a row fails, it is redirected to a file, but the rest is inserted into destination table.
Hope this helps.
Regards
Piotr
...and your only reply is slàinte mhath
October 23, 2009 at 12:43 pm
thanks a lot, but there is an example with that way/.....................
May 8, 2015 at 11:18 am
I have used this technique with all versions of sql server up to 2014, and none of them return the detailed error description raised by the database engine that you get when the package fails. This is the biggest limitation of SSIS, and a common requirement of many ETL processes that can be done with many other ETL tools.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply