It seems like the issue is column level vs row level errors. The method call is showing predefined errors, but does not return the same message you see from the db engine. Here are two examples.

1. I have a unique index setup on a table. When we try to insert a dup, the error raised by the db engine is "Cannot insert duplicate key row in object 'TableName' with unique index 'Index_Name'. The duplicate key value is ...." The error we get in SSIS is "The data value violates integrity constraints. "

2. I have a trigger on a table that checks for overlapping dates, and raises a 16 level error with rollback. Instead of getting the error message generated in the trigger, we get "No status is available".

In searching for a method to return the error generated by the db, I find that it is not possible in SSIS. What makes it even more disappointing is that we know SSIS does have these errors at the package level because we get them in a package level OnError event handler if the package is setup to fail on error, but there is apparently no way to have this information at the row level during a redirect. I hope I am wrong about this, but at this point I have not found a way to do it in SSIS even though it would be a simple tsql script to accomplish the same thing.