November 10, 2011 at 11:21 pm
Hi Guys
I am getting the following error when running an integration services
project to insert and Update data from one sql server database to another sql
server database: "Violation of Primary Key constraint". Cannot insert duplicate key in object.
Is there a way to check if the key is unique prior to trying to
transfer the data? Once I get one error it stops and none of the data
is transfered. The project is designed to be ran every few weeks to
update the destination data to match the source for reporting
purposes.
Any help would be greatly appreciated.
Thanks...
November 14, 2011 at 12:46 am
There are a few options:
* you can first check if the primary key exists or not. You can do this with TSQL, using a self-join or by using EXISTS, or you can use a LOOKUP component in the dataflow.
* you can put error handling on the destination component. Route all the rows that give the error to a flat file or an error dump table. But be aware, you cannot do this with the fast load option, as that option tries to insert all the rows in one batch. This has significant impact on performance.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
November 16, 2011 at 7:56 am
Like Koen said, I'd recommend using the Lookup transform to direct only rows that find no match to your destination.
But you'll need to do one more thing to - and that is, to use a sort transform to remove duplicates before inserting, since the lookup will not prevent you from inserting duplicates in your original batch. alternatively of course, you could just change your input query to a SELECT DISTINCT, or find some other way of eliminating duplicates.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply