Error Handling in SSIS Packages

In SSIS errors may occur because of the following reasons.
  1. Data conversion errors
  2. Lookup 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 in SSIS

 
There are two types of errors in SSIS.
  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 are trying to convert alpha-numeric string to an integer. This will result in a data conversion error.

    We can configure the SSIS component 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. 
 
1.gif 
 
Step 2: Connect the error output to Flat file destination input . 
 
2.gif 
 
Step 3: Configure Error output as Redirect Row.
 
3.gif 
 
Step 4: Configure Flat file Destination
 
4.gif 
 
Step 5 : Click New and select delimited in Flat File Format option.
 
5.gif 
 
Step 6 : Give valid file path and press OK .
 
6.gif 
 
Step 7: From the Flat File Destination Editor, click on Mapping option.
 
7.gif 
 
Step 8 : Click Ok.
 
Step 9 : Now run the package.
 
8.gif 
 
Step 10 : Error Details. Open the file and you can see the Error Code and Error details
 
9.gif