Duplicate key error for updated SCD1 output

Topics: Issues 2: Using the Component (Design-Time)
Jul 29, 2010 at 5:35 PM

I'm currenty using SQL Server 2005 Enterprise SP3, Kimball SCD 1.5.  I have a flat file source containing inserted/updated records from another system that I'm synchronizing in a data warehouse using the Kimball SCD.  I'm currently modifying the existing records with the changes using the SCD1 output.  The business key on the dimension source and the flat file source is being compared properly and records are being sent through the Updated SCD1 output when changes are detected, but when the records try to commit in the destination object the operation fails with the following error:  Cannot insert duplicate key row in object 'xxx.yyy' with unique index 'yyy_uni_idx'."  The only index defined in the table is the business key and it's a unique clustered index.

I've double-checked all of my component settings, but I'm unable to find anything incorrect.  I've watched the operation via profiler and I noticed that the data routed through the Updated SCD1 output is being written to the database via a bulk insert.  Shouldn't this be an update?  Can anyone please help me to shed some light on what is happening?

Jul 29, 2010 at 6:42 PM

I resolved my own problem here.  I was using the incorrect destination for the records being routed through the Updated SCD1 output.  I was using an OLE DB destination instead of using an OLE DB Command to update the rows.  Sorry for any confusion.

As an aside, I've just recently latched on to this component for managing SCD's within a data warehouse for an enterprise level company that I'm currently consulting for.  This component is unbelievably awesome!  Thanks Todd for all of your hard work and dedication in putting this functionality together.

Jul 31, 2010 at 2:54 AM

Thank you very much for the compliments!

Your issue reinforces the fact that I need to put together better documentation...