SSIS Data Flow Error Handling

Introduction

If I am taking about the data source of real world, no data source is perfect. That means we have to handle the anomalies and bad data when we process the data in SSIS data flow. That's the reason the SSIS data flow error handling is important. It is article I am trying to discuss about the Error handling portion of SSIS data flow. Hope it will be useful and informative.

Data flow Path

In my previous articles we see the precedence constraints of control flow. The path of the data flow is similar to that except the data flow path handles the row of data rather than the execution status.

There are two primary types of data flow paths

1.    The green color connectors that connects one component to another.

2.    Error paths are the read in color and connect between components.

Please note that not all components are containing the error path as they are not performing any operation on the data itself so there is no possible point of failure. For example, the multicast components. They only copies data.

Why the SSIS Error Occurs

The error may be occurs in the SSIS components for the several of reasons. Some common reasons are motioned bellow.

1.    The data conversion error

2.    For the Expression evolution error

3.    Look match error

Types of the Error

There are two types of error found in the SSIS.

Truncation:  A truncation generates results that might be usable. For example, if we are trying to insert 15 character values into 10 charter length column.

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

Configuring Error Output

To use the error path we need to configure the error output. There are three error handling options for handling error in the data flow components

Error Handling Options	Description
Fail Component	Setting the error output to this causes 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.

Example of SSIS Data Flow Error Handling

Here I am taking a simple example to understand it properly. Here I use just screen shot which is self describing.

Step-1 [ The Case Study ]

Here we have Text File name Test.

We are going to extract the flat file in my SQL Server Database Table Object. The script of the SQL server table objects are mentioned bellow.

CREATE TABLE Tbl_MYEPLOYEERECORDS

(EMPID          VARCHAR(1),

 EMPFULLNAME    VARCHAR(150),

 EMPGRADE       VARCHAR(1))

If any error came during the extraction it Logged the error in our text file name "ErrorOutput".

Step-2 [ SSIS Design ]

Step-3 [ Editing Flat File Source ]

Step-4 [ Editing Flat File Destination for Error Log Saving  ]

It just like Editing Flat file Source

Step-5 [ Generate Error by editing the Text File and Execute Package ]

Hope you like it.

Posted by: MR. JOYDEEP DAS