SSIS: Using the Script Component to Obtain an Error Description

Tuesday, December 27, 2011
by jsalvo

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.

SNAGHTML5b0de6

  • 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.

image

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.

image

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.

SNAGHTML6dd200

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

SNAGHTML6f8e1d

Modify the Input0_ProcessInputRow function (this example uses C#)

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

Comments

comments powered by Disqus