http://www.sqlservercentral.com/blogs/kyle-neier/2008/11/26/workaround-primary-key-violations-in-ssis/ Printed 2017/01/22 06:24AM
Workaround Primary Key Violations in SSIS
2008/11/26Before you get much further, please do not use this for every case where you have issues with duplicate rows. In most cases, the issue is in the source query and should be resolved there.
However, if you've encountered issues like I just did, this lazy DBA's approach might prevent you from banging your head against a wall.
I'm receiving a text file from a mainframe system. This file is fixed width and seemed easy to import. Unfortunately, the values in the file are duplicated and cannot be fixed.
I entertained the idea of staging tables and of redirecting the error rows, but eventually stumbled across the "Sort" transformation. There is a small checkbox in the lower corner that says "Remove rows with duplicate sort values". Voilà, no more duplicate key violations.
With the addition of a single transformation, the package is now just as simple as I originally had expected it to be.