Getting Error Description and Error Column Name in SSIS 2016...

If you check the Error Output Path of a SSIS Data Flow component, it only contains the ErrorCode and the ErrorColumn (actually this the ID value of the column). By looking at those two values, we cannot get a clear idea about the error.

I checked how it is possible to get the Error Description and the Error Column Name and found the below detail post.
https://www.mssqltips.com/sqlservertip/4066/retrieve-error-column-in-ssis-2016/

But that post is written before the SQL Server 2016 is released and due to that there are some differences for the Code.

In this post I am going to provide the code and some enhancements done for how to get the Error Description and Error Column Name for SSIS 2016.

To get the Error Description and the Error Column Name, for the Error Output path, you have to add a Script Component.
Sample implementation will be as below, where we
- Union all the error records
- Get the Error Description and Error Column Name using a Script Component
- Write the error records with those details to a CSV file

In the Script Component, ErrorCode and ErrorColumn are selected as Input Columns

As Output Columns we need to add columns as ErrorDescription and ErrorColumnName, where the relevant values will be assigned in the code.

Then to add the require code, click on the Edit Script

In that the added code is as below

public override void Input0_ProcessInputRow(Input0Buffer Row)
    {
        /*
         * Add your code here
         */
        Row.ErrorDescription = this.ComponentMetaData.GetErrorDescription(Row.ErrorCode).Trim();
        var componentMetaData130 = this.ComponentMetaData as IDTSComponentMetaData130;

        string errorColumnName = "";

        if (componentMetaData130 != null)
        {
            if (Row.ErrorColumn > 0)
            {
                errorColumnName = componentMetaData130.GetIdentificationStringByID(Row.ErrorColumn);
            }
            else
            {
                errorColumnName = "NA";
            }

        }

        if (errorColumnName != "NA")
        {
            string searchStr = "Columns[";
            int lenSearchStr = searchStr.Length;
            int startIndex = errorColumnName.IndexOf(searchStr) + lenSearchStr;
            errorColumnName = errorColumnName.Substring(startIndex);
        }

        Row.ErrorColumnName = errorColumnName;
    }

If you just get the column name using the GetIdentificationStringByID method, the output will be as below.
OLE DB Destination.Inputs[OLE DB Destination Input].Columns[COLUMN_NAME]

Actually for the who ever managing the data, they do not need to know the SSIS Component which produced the error and they will be only interested about the column name and the error.
That is why only the Column Name is extracted using the below section in the above code

if (errorColumnName != "NA")
        {
            string searchStr = "Columns[";
            int lenSearchStr = searchStr.Length;
            int startIndex = errorColumnName.IndexOf(searchStr) + lenSearchStr;
            errorColumnName = errorColumnName.Substring(startIndex);
        }

After that error records will be written to a CSV file and we can share it with responsible parties.