In the SSIS data flow, many data flow components provide an ErrorOutput that allows you to route rows that generate errors or truncation to another component in the data flow.  The ErrorOutput path contains the following metadata: ErrorCode, ErrorColumn and Flat File Source Error Output Column.

• Flat File Source Error Output Column: The data row that generated the error.
• ErrorCode: Code associated with the error that occurred.
• ErrorColumn: Numeric ID of the column that caused the error.

Obtaining the ErrorDescription requires a bit more work, but can be easily accomplished using the data flow Script Component.

The first step is to add a  ‘Script Component’ to your data flow and connect the Error Output as an input to the script component as shown below.

Double click on the script component to edit.

Under the ‘Input Columns’ tab, check the ErrorCode column (you may also include additional columns if you wish to use them in subsequent data flow components).  This column is required to obtain the Error Description.  The ‘Usage Type’ should be set to ReadOnly.

Next, select the ‘Inputs and Outputs’ tab.  Expand ‘Output 0’ and select ‘Output Columns’. Click the ‘Add Column’ button.  In the ‘Name’ field, enter a descriptive name such as ErrorDescription.  Set the DataType field to string [DT_STR] and the length to 255.

Click on the ‘Script’ tab and then click the ‘Edit Script’ button.

Modify the Input0_ProcessInputRow function (this example uses C#)

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