ARTICLE

Demo 2 Error Handling in SSIS Packages

Posted by Muralidharan Deenathayalan Articles | SQL Server May 16, 2011
In SSIS errors may occur due to either Data conversion errors, Lookup errors or Expression evaluation errors.
Reader Level:
Download Files:
 


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.
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

COMMENT USING

Trending up