SSIS - How To Redirect Rows From OLE DB Destination in SSIS Package

Scenario:

We are loading some data from flat file source to SQL server Table. There is possibility that the type of source data does not match with destination column data type. In those cases we want to ignore or redirect those records from destination and load the correct data. 

Solution:

OLE DB Destination does provide the facility to redirect rows in case they can not be inserted due to truncation error, constraint issue etc. Use the below data in Flat File source.

ID,Name,Address
1,Aamir,XY Address
2,Raza,XX Address
test,Robert,344 Address
3,Williams, 123 streeet Address
4,Test,test address
5,Test name, test address 1
6,Test Name 2, Test Address 2

If you notice that instead of having number in ID ,I have put test. That means we can not insert this record in table with below definition as ID is int type.

CREATE TABLE [dbo].[Test](
   [ID] [int] NOT NULL,
   [Name] [varchar](100) NULL,
   [Address] [varchar](100) NULL
) 

I have create an SSIS Package in SSIS 2012 and SSIS 2008. The Data Flow in both versions has Flat File Source and OLE DB Destination. I was just checking the behavior of both are same or not without implement redirection records. Both are create with default value. I did not make any change to any property.
Fig 1: Data Flow Task with Flat File Source and OLE DB destination in SSIS 2008

Fig 2: Data Flow Task with Flat File Source and OLE DB Destination in SSIS 2012

Let's run the SSIS the Package that we have created in SSIS 2008.
Fig 3: SSIS 2008 Data Flow Task without Redirecting Rows from OLE DB Destination

As we can see that it read 7 rows and then failed. Let's check the destination table and see if it loaded any records or all failed to load.
Fig 4: Table Loaded by SSIS 2008 without Redirecting Rows

We can see that the two top records before the error are loaded successfully to the test table. Let's truncate destination table and try to load data to this table by using SSIS package that we created in SSIS 2012.

Fig 5: Table Loaded by SSIS 2012 without Redirecting Rows from OLE DB Destination

Let's check if any rows are loaded in table.
Fig 6: Record Count after Execution of SSIS 2012 Package

As we see that that there was no record loaded into table when we used SSIS 2012 Package. 

Note: 
When you design SSIS Package in 2008 or SSIS 2012, keep this behavior in mind so can take care of it properly.



Configure Redirect Rows From OLE DB Destination

Now let's configure the OLE DB Destination to redirect any rows those can not be inserted due to any reason.

Double Click on OLE DB Destination and then configure as shown in below fig.
Fig 7: Configure OLE DB Destination for Redirect Rows

After configuring OLE DB Destination, You can write the redirect rows to any destination. For Test Purpose I have redirected them to Multicast Transformation. 

Fig 8: Redirect Rows from OLE DB Destination in SSIS Package

As we can see that total 7 records are read from source and 6 are loaded in destination table and one record is redirected as it can be not loaded into table because ID is int type and data coming in is varchar.


One More Test Case

Let's make some changes in our destination table and consider that the Name column is unique. I dropped the table and created by using below DDL

CREATE TABLE [dbo].[Test](
   [ID] [int] NOT NULL,
   [Name] [varchar](100) NULL,
   [Address] [varchar](100) NULL,
 CONSTRAINT [C_Name] UNIQUE(Name))

Also I made changes in Sources data. You can see the changes in Red, I have duplicate Name now in two records.

ID,Name,Address
1,Aamir,XY Address
2,Raza,XX Address
test,Robert,344 Address
3,Williams, 123 streeet Address
4,Test,test address
5,Test name, test address 1
6,Raza, Test Address 2

Now Let's run the same SSIS Package and see what happen?
Fig 9: Redirect Rows from OLE DB Destination with Unique Constraint Violation

We can see that all the rows are redirected. so what we learnt from this

If the row data can not be inserted due to truncation or overflow then It will be redirect only that row. But if It is Constraint Violation such as Unique, Primary key etc. then it will redirect entire batch. 

To handle this type of situation , we have to decrease the batch size to 1. This can make the load slow. It is better to validate data before loading into destination and redirect from there instead of redirecting from Destination.
Fig 10: Set Maximum Insert Commit Size to 1 in OLE DB Destination for Redirecting Rows

Let's run our SSIS Package now and see if all working as expected. It should only redirect two rows , one with bad data and other due to Unique Constraint.
Fig 11: SSIS Package Redirecting Rows from Destination Transformation

While you develop your SSIS Package, Keep in mind if you are redirecting your rows from destination just because of overflow or due to constraints. For both scenarios the behavior is different.