Reader Level:

Demo 2 Error Handling in SSIS Packages

By Muralidharan Deenathayalan on May 15, 2011
In SSIS errors may occur due to either Data conversion errors, Lookup errors or Expression evaluation errors.

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




JetBrains ReSharper Ultimate
.NET code analysis, refactorings, navigation, unit testing, code coverage and profiling in Visual Studio, a bonus for C++ devs, bundled at 50% off.

Trending up