Error Handling

 When a data flow component applies a transformation to column data, extracts data from sources, or loads data into destinations, errors can occur. Errors frequently occur because of unexpected data values. For example, a data conversion fails because a column contains a string instead of a number, an insertion into a database column fails because the data is a date and the column has a numeric data type, or an expression fails to evaluate because a column value is zero, resulting in a mathematical operation that is not valid.

Errors typically fall into one the following categories:

·        Data conversion errors, which occur if a conversion results in loss of significant digits, the loss of insignificant digits, and the truncation of strings. Data conversion errors also occur if the requested conversion is not supported.

·        Expression evaluation errors, which occur if expressions that are evaluated at run time perform invalid operations or become syntactically incorrect because of missing or incorrect data values.

·        Lookup errors, which occur if a lookup operation fails to locate a match in the lookup table.

Use error outputs to capture row-level errors

Many data flow components support error outputs, which let you control how the component handles row-level errors in both incoming and outgoing data. You specify how the component behaves when truncation or an error occurs by setting options on individual columns in the input or output. For example, you can specify that the component should fail if customer name data is truncated, but ignore errors on another column that contains less important data.

The error output can be connected to the input of another transformation or loaded into a different destination than the non-error output. For example, the error output can be a connected to a Derived Column transformation that provides a string for a column that is blank.

The following diagram shows a simple data flow including an error output.

 

Errors are either failures or truncations

Errors fall into one of two categories: errors or truncations.

 

Errors. An error indicates an unequivocal failure, and generates a NULL result. Such errors can include data conversion errors or expression evaluation errors. For example, an attempt to convert a string that contains alphabetical characters to a number causes an error. Data conversions, expression evaluations, and assignments of expression results to variables, properties, and data columns may fail because of illegal casts and incompatible data types.

Select an error handling option

You can configure how sources, transformations, and destinations handle errors and truncations. The following table describes the options.

 

SELECT AN ERROR HANDLING OPTION

Option          Description

Fail Component       The Data Flow task fails when an error or a truncation occurs. Failure is the default option for an error and a truncation.

Ignore Failure          The error or the truncation is ignored and the data row is directed to the output of the transformation or source.

Redirect Row The error or the truncation data row is directed to the error output of the source, transformation, or destination.

Get more info about the error

In addition to the data columns, the error output includes the ErrorCode and ErrorColumn columns. The ErrorCode column identifies the error and the ErrorColumn contains the lineage identifier of the error column. 

Under some circumstances, the value of the ErrorColumn column is set to zero. This occurs when the error condition affects the entire row instead of a single column. An example is when a lookup fails in the Lookup transformation. 

These two numeric values may be of limited use without the corresponding error description and column name. Here are some ways to get the error description and column name. 

You can see both error descriptions and column names by attaching a Data Viewer to the error output. In SSIS Designer, right-click on the red arrow leading to an error output and select Enable Data Viewer. 

You can find column names by enabling logging and selecting the DiagnosticEx event. This event writes a data flow column map to the log. You can then look up the column name from its identifier in this column map. Note that the DiagnosticEx event does not preserve whitespace in its XML output to reduce the size of the log.