Retrieving the SQL Server error message in Error output

Hello,

I've read http://blogs.conchango.com/jamiethomson/archive/2005/08/08/1969.aspx

that explains how to use scripting and ComponentMetaData.GetErrorDescription() to get the SSIS error descriptions. But this is not always sufficient.

What should I do to retrieve the original SQL Server error messages ?

TIA.

--
JeromeI assume you are inserting into a SQL Server table when the error occurs. If this is not the case please advise.

If it IS the case, the error message will be captured in whatever method you are using for logging. Have you configured a logging provider?

Regards
Jamie|||

Hello Jamie !

Jamie Thomson wrote:

I assume you are inserting into a SQL Server table when the error occurs. If this is not the case please advise.

Yes I do. Using either an "OLE DB Destination" or a "OLE DB Command".

Jamie Thomson wrote:

If it IS the case, the error message will be captured in whatever method you are using for logging. Have you configured a logging provider?

Regards
Jamie

Using logging, I get the original SQL Server error message. As I want.

The problem comes with error outputs. Error messages get "translated" by ComponentMetaData.GetErrorDescription():

Some examples :

1) UK violations

"Violation of UNIQUE KEY constraint 'UK_Brand_Name'. Cannot insert duplicate key in object 'dbo.Brand'."

gets turned into

"The data value violated the integrity constraints for the column."

2) CHECK constraints violations

"CK_Test_SSIS". The conflict occurred in database "Test", table "dbo.Test_SSIS", column 'i'."

into

"The data value violates integrity constraints."

3) Custom errors

A RAISERROR in a stored procedure or
a RAISERROR followed or not by a ROLLBACK in a trigger

will be turned into

"No status is available."
Is there any other solution to retrieve the exact error message sent by SQL Server ?

TIA.

--
Jerome

|||I cannot think of a way of doing this in the pipeline. Errors that are raised from SQL Server are errors that SSIS does not know about so I can't see how you could get the error message other than using that which is returned as I explained before.

-Jamie