How to handle Failed Rows in a Data Flow

Suppose you encounter the following scenario:


You have a source database table and destination database table, you want to transfer rows from source destination so you decide to use the SSIS data flow task with an OLE DB source and OLE DB destination. When you run the task, an error occurs when destination table has a few rows inside, and there is Unique Constraint in ID field of Destination table.
In this situation your data flow will fail, because when OLE DB Destination want to insert rows in destination table, it will lead to a Unique Constraint error and will raise Data Flow error.  In turn, this will prevent any rows from being inserted into the table, even rows which hasn't equivalent key in destination !

So, a work around needed here to avoid failing package and to move only rows which hasn't problem with unique constraint in destination table.

Let me explain it with a sample here...


Create source table with this script:
 

CREATE TABLE [dbo].[SourceFailureTable](
                          [ID] [int] IDENTITY(1,1) NOT NULL,
                          [name] [varchar](50) NULL,
                       CONSTRAINT [PK_SourceFailureTable] PRIMARY KEY CLUSTERED
                      (
                          [ID] ASC
                      )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF,
                      
                      ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
                      ) ON [PRIMARY]
                      

Select all Open in new window

And fill it with this data:
  1.jpg

Now create destination table with this structure:
 

CREATE TABLE [dbo].[DestinationFailureTable](
                          [ID] [int] NOT NULL,
                          [name] [varchar](50) NULL,
                       CONSTRAINT [PK_DestinationFailureTable] PRIMARY KEY CLUSTERED
                      (
                          [ID] ASC
                      )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF,
                      
                      ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
                      ) ON [PRIMARY]
                      

Select all Open in new window

Note that DestinationFialureTable, has a Primary Key on ID filed, primary key is basically a Unique Constraint. So this constraint will check IDs to be unique , and if an insertion tends to insert a not unique value in ID field this will cause Unique Constraint Error.

Fill Destination with these three fields:
  2.jpg

You want to import rows from SourceFailureTable to DestinationFailureTable.
Create a simple Data flow task, with an OLE DB Source, connected to SourceFailureTable. And an OLE DB Destination connected to DestinationFailureTable, map the columns too.
Now every time you run this package you will got this error:
  3.jpg

Error description:
"Violation of PRIMARY KEY constraint 'PK_DestinationFailureTable'. Cannot insert duplicate key in object 'dbo.DestinationFailureTable'."

And none of ten rows in Source table moved to destination table, even rows with IDs like 1 , 2, 4 ,.. which not exists in the destination table.

So, What you can do?
Right click on OLE DB Destination, select properties window. Find AccessMode in the properties, you can see that the AccessMode value is OpenRowSet using FastLoad. This will cause that all records from input insert with a fast load transaction at one time. So this will cause error.
Change AccessMode to OpenRowSet . This option force OLE DB Destination to insert data row by row.
Note that this is not good option when you insert large amount of records, because insertion with fast load has better performance, but in this sample I just want to show you how to handle failed rows in insert.
After chaning AccessMode to OpenRowSet , run package again. You will see the error happens again with no luck. But difference is here in the destination table:
  4.jpg

In the destination table two new rows inserted , rows with ID 1 and 2, but no more rows. The meaning of this behavior is that OLE DB Destinaion tried to insert data row by row into the DestinationFailureTable. First two rows inserted successfully. but for third row it hits unique constraint error again and cause package fails. Failing the package stops other rows transformation.

So you should handle rows which cause Error. This is the time when Failure Error Output comes to help.
First delete rows with ID 1 and 2 from DestinationFailureTable manually.
Then add another table named FailedRows with this structure:
 

CREATE TABLE [dbo].[FailedRows](
                          [ID] [int] NULL,
                          [name] [varchar](50) NULL
                      )
                      

Select all Open in new window

You supposed to move rows which cause error to this table.

Now add another OLE DB Destination in data flow right after DestinationFailureTable ,and connect RED ARROW to it. When you connect this arrow, a Configure Error Output window will appear. Look at this screen shot for more details:
  5.jpg

There are three options for the Error property in this window:


Ignore Failure    Will ignore any errors during insertion of records to destination
Redirect Row    Will redirect any rows which cause error during insertion to destination
Fail Component    Will cause to fail component when insertion hits any error

By default this option set to Fail Component, so when you got an error during insertion the whole OLE DB Destination will fail.
Set the Error as Redirect row. This will cause every rows which cause error ( means BAD rows ) to be redirected to new OLE DB Destination,
now hit ok. Double click on new OLE DB Destination, let's name it FailedRows. Connect this to failed rows, and map the columns like below:
  6.jpg

Note that there are two new columns in mapping page, ErrorCode and ErrorColumn. these are auto generated columns by Failure Error Output and will show code and column number of error. You don't need them in this sample, so just map ID and name.

Now run the package. You will see that there are 10 rows transferred from SourceFailureTable table to the DestinationFailureTable table , but there are only 3 rows transferred to FailedRows. This means that there are 3 rows existing in DestiantionFailureTable, which prevent equivalent values in SourceFailureTable to be inserted . These rows are thus transferred to the FailedRows Table.
This is full schema of package:
  7.jpg

And look at results in DestinationFailureTable:
  8.jpg

There are 7 new rows in this table.

And this is results in FailedRows Table:
  9.jpg

There are 3 rows which cause unique constraint failure transfered to this table.

This was a sample of how to use failure error output in SSIS. I hope this helps you solve some issues in this area!