SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Workaround Primary Key Violations in SSIS

Before 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.

Kyle Neier

Kyle has been working with computers professionally since 1995 in both a development and administrative capacity. For over 10 of those years, SQL Server has been the focus of both a professional and personal passion. Kyle has worked in online retail, manufacturing, EMR software, and pharmaceutical industries. Kyle joined PTI in 2011 as a Senior Microsoft Consultant where he continues to chase his passion of automating DBA processes and doing everything humanly possible to prevent the 0200 page. Kyle serves as the Vice-President of the Indianapolis chapter of PASS (www.indypass.org) and served on the committee to bring SQL Saturday to Indianapolis. He shares his passion for SQL Server with others through speaking and mentoring at local and regional events.


Leave a comment on the original post [sqldbamusings.blogspot.com, opens in a new window]

Loading comments...