Demo 2 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:
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 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.

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


Step 3: Configure Error output as Redirect Row.


Step 4: Configure Flat file Destination


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


Step 6 : Give valid file path and press OK .


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


Step 8 : Click Ok.

Step 9 : Now run the package.


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