ETL SSIS : Redirecting error rows to a seperate table

Question

I am working on a package that contains a Source, about 80 lookups and 1 destination.

The data in the source table is not consistent enough and hence my package fails very often.

Is there a way by which I can transfer all the rows which are giving at the time of inserting them in destination table?

For eg. I have 5 rows in Source and out of which 1st and 4th will give error. Now the result should be that 2nd, 3rd and 5th should go in destination but 1st and 4th should be stored in some flat file or a db table.

Thanks in advance

Answer 1

You can create a second OLE DB Destination and direct the red arrow from your Data Conversion task to this destination. Then choose Configure Error Output in your Data Conversion task and choose Redirect row as an option for all Errors/Truncations. Finish the new destination mapping and you're done: data that is correct should now go to your first destination (green arrow) and incorrect data should go to your second destination (red arrow).