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.

comments powered by Disqus