Decoding Errors with Error Column Names and in SQL Server 2016 Integration Service

Problem: When SSIS packages failed at the step of loading files, sometimes it doesn't tell which column is the one that caused failure. How to find out the troublemaker?

Solution: 

If you have SQL Server 2016 Integration Service or later version, you can retrieve error column names by using the new GetIdentificationStringByID method in SSIS 2016.

Below is an example showing how to use IDTSComponentMetaData130.GetIdentificationStringByID method to decode error column names and error descriptions by using Script Component.

Step 1: Choose script component and connect it with error output from upstream.
Step 2: Select Transformation as script component type.

Step 3: After selecting C# as script language to use, check ErrorCode and ErrorColumn as Input Columns as shown below.

Step 4: Add output columns ErrorColumnName and ErrorDesc to represent error column names and descriptions respectively, then specify the column type and length.

Step 5: Edit script and added this following to Input0_ProcessInputRow function:

public override void Input0_ProcessInputRow(Input0Buffer Row)
    {
        
        IDTSComponentMetaData130 componentMetaData = this.ComponentMetaData as IDTSComponentMetaData130;
        Row.ErrorColumnName = componentMetaData.GetIdentificationStringByID(Row.ErrorColumn);
        Row.ErrorDesc = ComponentMetaData.GetErrorDescription(Row.ErrorCode);
    };

Then you can add a flat file output to write the errors with detailed information about error column names and descriptions.

From the following output, you can tell that the loading failure is because input column "OLE DB Destination.Inputs[OLE DB Destination Input].Columns[OrderDate]". The error description is "The data value cannot be converted for reasons other than sign mismatch or data overflow."