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 🙂

 


 


 

SSIS: ErrorDescription and ErrorColumn (ErrorColumnName)

If you’re looking for a way to add the logical error description into your dataflow, you may want to use a common Script Component with these lines of Visual C# script (overwrite the existing procedure):

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

A detailed manual to accomplish this can be found here, thanks to Jamie Thomson.


But if you also want to add the specific column name on which the error occured, take a look at this customer SSIS component.

EOD logo

EOD in action:

EOD’s output:

EOD’s properties:

Error Output Description Properties

How to install this BIDS 2008R2 component?
Step 1) Extract the ZIP-file
Error Output’s Description on CodePlex

Error Output’s Description

Here’s a rebuild version of the original version 2.0.0 ErrorOutputDescription.dll library (Windows 7 and VS2010) which should be more friendly in Win7:
(Rename the file extension to *.zip, WordPress doesn’t allow ZIP-files)

Right click to download and Save As EOD.zip

Error Output Description - Required Files


Step 2) Open a command-windows (search for ‘cmd’) and ‘Run as Administrator’, then execute the Install.bat file.

Install Error Output Description as Administrator


Step 3) Enable the custom component in BIDS so that it will be listed in the Toolbox:
Go to Tools -> Choose Toolbox Items (in BIDS)

Error Output Description Enabled in BIDS

Select the Error Output Description’s DLL and click on ‘OK’.
It should now be selectable in the Toolbox:

Error Output Description Component in BIDS


If you’re having problems with this component (it can be a bit buggy), a quick work around is to delete all dataflow components down-stream of the EOD-component and possibly rebuild the package, before you edit the EOD-component.

Other discussions about this custom component can be found here.



But…after all…it works great once you get it running 😀
You may want to use the column name to zoom in on the incorrect values which were found during processing.

A practical example of such a report can be found here.

Error Output Description - Error Report