Archive for November, 2017

I have an SSIS package that does SCD type 2, i.e. when there is an update, it inserts a new record and then expires the previous record. In testing it, I updated one record but when the package was run, the INSERT has failed with the error saying that the primary key constraint has been violated and cannot insert duplicate key in object. I checked my code and the checksum making sure that the incoming record and the existing record have different checksums. Indeed they both have different checksums. So this does not make any sense.

I talked about this with my colleague and he Googled it and found a solution (https://stackoverflow.com/questions/20153310/identity-not-generating-the-seed-and-increment-correctly)  that worked for my case. Thanks to the user that has shared the fix. 🙂

The solution is to reseed the table by running the following SQL:

DBCC CHECKIDENT(‘Id_Table’, RESEED, 0)    —- Reseed to any smallest number

DBCC CHECKIDENT(‘Id_Table’, RESEED)      —- Reseed without providing any seed value

Reseed the table to Zero and then just execute the DBCC command without any seed value and it will reseed the Identity value to next highest available Identity value. You do not need to truncate the table to reseed.

Even though the solution has fixed the problem, however, it keeps recurring.

I had another chat with my colleague at work. He said he has seen this before and provided me with the fix.

The fix is to insert the default records (where the UIDs are -1, -2, -3) first then load the records from the source into the destination table in the SSIS package. UID = -1 is unknown, -2 is no value and -3 is invalid.


Read Full Post »

%d bloggers like this: