In SSIS error may occur because of the following reasons.

1) Data Conversion errors

2) Look match errors

3) Expression evaluation errors

Dataflow components support row-level error details in both the input as well in the output data. One error output can be connected as the input for the other data component.

Types of errors:

There are two types of errors in SSIS.

1) Truncation

2) Error.

1) Truncation:

A truncation generates results that might be usable. For example, if you trying to insert 10 character values into 5 charter length column.

2) Error:

This is failure of the SSIS component and it generates the NULL result. For example: If you trying to convert alpha-numeric string to Integer. This will result data conversion error.

We can configure the SSIS component how to handle the error and truncation in SSIS transformations.

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.

How to Configure Error handling in SSIS ?

Step 1: Drag and drop and file Flat file destination component.

Error1.png

Step 2: Connect the error output to Flat file destination input .

Error2.png

Step 3: Configure Error output as Redirect Row.

Error3.png

Step 4: Configure Flat file Destination

Error4.png

Step 5 : Click New and select delimited in Flat File Format option.

Error5.png

Step 6 : Give valid file path and press OK .

Error6.png

Step 7: From the Flat File Destination Editor, click on Mapping option.

Error7.png

Step 8 : Click Ok.

Step 9 : Now run the package.

Error8.png

Step 10 : Error Details. Open the file and you can see the Error Code and Error details

Error9.png