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.

My first blog post -- starting small

I've been hanging around the SQL PASS Summit this week and came to a realization, I do know something...

I don't know it all, but I don't know nothing, I'm definitely somewhere in between. So, I hope to share these thoughts and hopefully help out people in similar ways as I've received (primarily) and given (in small portions) this week.

I'm a SQL Server DBA and have every aspiration of keeping it that way. Through the lifetime of this blog (could be short; could be long) I hope that anyone who cares (all 3 of them, plus my kids) will walk away feeling a little more enlightened.

I'll start short.

One of the small tidbits I've gleaned this week is that doing what one would believe to be an innocuous shrink operation of tempdb can cause corruption; necessitating instance restart. The proper method is to bring the instance into single user mode first. For more information, hear it from the horses mouth: http://support.microsoft.com/kb/307487#6

Good luck and happy DBA'ing. :-)