Duplicate surrogate key??

[jrmodi]

Hi,
I am using a dimension build to load a dimension table. I have created a reference structure in which I have template that contains surrogate key and business key. The surrogate key has a start value of 1. Now when I execute the build for the first time it runs fine and the surrogate keys are generated all fine. If I change the source file to point to a new database that contains different data than the previous one then I would expect new surrogate key to be generated for any new record. To my surprise I get a primary key violation error (I have a unique constraint on PK). Looking at the logs it is evident that DM runs an SQL to get max surrogate key however it still gets the error. Here is some related extract from the log
select max("SKEY_CUST") from "DWH"."CUSTOMER"
insert into "DWH"."CUSTOMER"...............
And then the below error is thrown

DS-DBMS-E400: UDA driver reported the following on connection 'DWH':
UDA-SQL-0107 A general exception has occurred during the operation "execute complete".
ORA-00001: unique constraint (DWH.CUSTOMER_PK) violated
I tried removing the primary key constraint only to see what data is being inserted and I see that new records were inserted using surrogate keys starting form 1...!!

I have followed all the steps mentioned in the DM user guide so not sure where I have gone wrong. Is there any anything else that I need to set in order to be able to use surrogate keys?

Thanks

[wyconian]

HI

First question is why do you need to change the data source?  If you want to have more than 1 data source for build you can add as many as you want then you may not get the duplicate key issue.

Presumably the surrogate key is based on a unique business key so it sounds like the second data source has the same business keys as the first source which could be why it's duplicating the skeys. 

You can either accept records with duplicate keys, reject them or merge them depending on what you want to do.  (These are options in the build properties/input tab).

Have you checked the data to see if the same business keys are in both sources?

[anshulsharma.ujn]

Hi jrmodi,

I am having the similar problem..the only difference is that I am upgrading from decision stream to data manager and my database is same.

Could you please suggest me how did you fix this error?

[MFGF]

How is the PK constraint defined on your target table?  Is it based only on the surrogate key?

MF.