SSIS 2016: Retrieving the ErrorDescription and ErrorColumnName

Since SQL Server 2016 there is a ‘built-in’ function to retrieve the actual column name of the column which caused the error. But there’s also a flaw, in case of a database constraint the ‘ErrorColumn’ (input parameter) will remain empty. So I’ve added an ELSE-statement to it in case it doesn’t receive a value:

How to set it up

1) Copy the script below

public override void Input0_ProcessInputRow(Input0Buffer Row)
{
Row.ErrorDescription = this.ComponentMetaData.GetErrorDescription(Row.ErrorCode);

IDTSComponentMetaData130 componentMetaData = this.ComponentMetaData as IDTSComponentMetaData130;

if (Row.ErrorColumn > 0)
{
Row.ErrorColumnName = componentMetaData.GetIdentificationStringByID(Row.ErrorColumn);
}
else
{
Row.ErrorColumnName = "Database constraint :)";
};
}


2) Paste it into the script window and save it


3) Select both ‘ErrorCode’ and ‘ErrorColumn’ as input parameters


4) Now add two new output columns as shown below

In my case both output column are of data type string (DT_STR) with a size 250.

5) Add a Derived Column to the Data Flow after the Script Component and use the following SSIS expression to replace the original output value of ‘ErrorColumnName’

REPLACE( REVERSE( SUBSTRING( REVERSE( ErrorColumnName ),1,FINDSTRING( REVERSE(ErrorColumnName) ,”[“,1) – 1) ),”]”,” “)

(The original output value also contains the component’s name and we’re just interested in the column name itself)

 

There you go!
Many thanks to my colleague Gigi for the SSIS Expression 🙂