Business Intelligence Blogs

View blogs by industry experts on topics such as SSAS, SSIS, SSRS, Power BI, Performance Tuning, Azure, Big Data and much more! You can also sign up to post your own business intelligence blog.

«September 2015»
MonTueWedThuFriSatSun
31123456
78910111213
14151617181920
21222324252627
2829301234
567891011

SSIS: Using the Script Component to Obtain an Error Description

  • 27 December 2011
  • Author: Jennifer Salvo
  • Number of views: 8976
  • 0 Comments

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);
}

Print
Categories: Analysis Services
Tags:
Rate this article:
No rating

Please login or register to post comments.