Salvo(z)

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

SSIS provides functionality to handle unexpected data in the data flow.  Data flow errors are often due to invalid data values (e.g. string instead of number) and data truncation. 

Many components in the data flow support error outputs that allow you to control how truncation and errors are handled.  The Error Outputs contain an ErrorCode and ErrorColumn in addition to the data columns in the row that caused the error.  Error outputs can be connected to another component in the data flow (e.g. a flat file destination to log the rows that generated errors).

You can control how data errors and truncation are handled:

1. Double click on the Data Flow component.

2. Select ‘Error Output’.

3. In the ‘Error’ and ‘Truncation’ columns, you specify how each scenario should be handled.  You may click in any cell to set the value or select multiple cells and then use the drop-down at the bottom of the window to set the value.

The options are:

• Ignore Failure – The row is routed to the  ‘Success’ / Default output.
• Redirect Row – The row is routed to the ‘Error’ output.
• Fail Component – The data flow task fails (this is the default option).

In the example below, the error output is being routed to a script component.  The script component is used to obtain a description of the error based on the error code (this blog post provides more details).  The errors are then routed to a flat file destination.

The ‘Flat File Destination’ is configured so the file is overwritten (so it doesn’t grow uncontrollably).

Under the “Mappings” tab, the following columns are written to the file.

• 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.
• ErrorDescription – Description of the error that occurred.  The ErrorDescription was obtained in the ‘Script Component’ using the GetErrorDescription function, more details are included in this blog post.

SSIS does not include an ‘out-of-the-box’ isNumeric( ) function.  Fortunately, this functionality can be implemented using the script component in the data flow.

I am going to make the assumption that readers are familiar with the SSIS script component.  If not, please feel free to read my prior blog post on the subject: SSIS Dataflow Script Component

To implement isNumeric( ) behavior, I used the C# Int32.TryParse method.

TryParse(String, Int32):  Converts the string representation of a number to its 32-bit signed integer equivalent. A return value indicates whether the conversion succeeded.

Here’s a snippet of the code in C#:

public override void Input0_ProcessInputRow(Input0Buffer Row)
{
    int BatteryQuantity1Int;

    if (! int.TryParse(Row.BatteryQuantity1Input, out BatteryQuantity1Int))
       BatteryQuantity1Int = 0;   

    //Add an output row and set the column values
    Output0Buffer.AddRow();
    Output0Buffer.ModelNumber = Row.ModelNumberInput;
    Output0Buffer.ModelYear = Row.ModelYearInput;
    Output0Buffer.ModelName = Row.ModelNameInput;

I also came across a blog post by Dustin Ryan where he implements IsNumeric( ) functionality using the Derived Column Transform.  The post is found here.