Redirect Error rows to log table

The most of the error in Data Flow task occur while loading data in destination table. Because the data in source is not always compatible with destination structure and that causes the failure of task. There are many reason of failure while loading data into destination file or table. Some of them are following:

1.       Truncation of data

2.       Mismatch of data type cause conversion error

3.       Primary key constraint failure because of duplicate data 

4.       NOT NULL constraint failure because of NULL value

5.       Foreign Key or check constraint failure

SSIS has option to redirect error rows to a log file or table. Let us see how it is configured.

At first create the required tables using below script:

CREATE TABLE tblSource (Col1 INT, Col2 VARCHAR(5), Col3 VARCHAR(55))

CREATE TABLE tblErrorRows (Col1 INT, Col2 VARCHAR(5), Col3 VARCHAR(55), 

ErrorCode VARCHAR(50), ErrorColumn VARCHAR(50))

CREATE TABLE tblDestinatin (Col1 TINYINT CONSTRAINT FK_Col REFERENCES tblPrimary (PK_Col), 

Col2 TINYINT NOT NULL PRIMARY KEY, Col3 VARCHAR(30), CONSTRAINT ck_Col4 CHECK (Col2 <10) )

CREATE TABLE tblPrimary (PK_Col TINYINT PRIMARY KEY)

INSERT INTO tblPrimary VALUES (1)

INSERT INTO tblPrimary VALUES (2)

INSERT INTO tblPrimary VALUES (3)

INSERT INTO tblPrimary VALUES (4)

INSERT INTO tblSource VALUES (1, '1', 'pass')

INSERT INTO tblSource VALUES (1, NULL, 'fail NULL not allowed')  

INSERT INTO tblSource VALUES (1, '1', 'fail duplicate not allowed')

INSERT INTO tblSource VALUES (5, '2', 'Fail FK_Col constraint')

INSERT INTO tblSource VALUES (2, '300', 'Fail max value exceeds limit')

INSERT INTO tblSource VALUES (2, 'a', 'Fail conversion error')

INSERT INTO tblSource VALUES (2, '1', 'Fail truncation error length of this string is >30')

 
Now in a SSIS package add a Data Flow task having following design
Oledb Source: Table: tblSource
Oledb Destination: Table: tblDestination
Oledb Destination for error output: Table: tblErrorRows
In the Oledb Destination Editor configure the Error Output to Redirect row as shown in below screenshot.

Now execute the SSIS package. We will find that out of 7 rows 6 rows are redirected to tblErrorRows table.

Now see the records of table in SSMS using below queries:

SELECT * FROM tblDestinatin

SELECT * FROM tblErrorRows

The output is shown in below screenshot:

So we are able to redirect all erroneous rows to a log file without failing the package.