Skip to Content

Generated Key Column and Key Generation Transform

Dec 29, 2016 at 11:52 AM


avatar image


I implemented a scenario in which I used source -> query -> table_comparison -> target. In target I added Cust_gen column and after job execution I found that it contains null values which is correct as I have not added any key generation transform to get the values.

Now after this I added Key generation transform to check how it actually works and added a row in source and after execution of job i found that cust_gen column has a set of values for every record but I have read that it looks for maximum existing value and then assigns value to newly inserted records. So as per my understanding the maximum value in source is 501 and the value of cust_gen should start from 501.

Can someone please help me that how does this Key generation transform works and also why every record has got values of cust_gen.



error-01.png (103.0 kB)
error-02.png (95.4 kB)
10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

3 Answers

Ravi kiran Pagidi Dec 30, 2016 at 11:03 AM

Key_gen transform looks for the max value in the target, not in the source.

Show 1 Share
10 |10000 characters needed characters left characters exceeded

Thanks Ravi!

But then why is it generating random values under Cust_gen column. Also now if suppose I insert 2 new records in my source and set generate key column to Cust_gen(which is not present in source) then what will be the output.

Can you please help!

One more clarification required: - Under generated key column we have to select that common column(which has unique value or is primary key) which is present in both source and target, right?

Ravi kiran Pagidi Jan 05, 2017 at 11:40 AM

Hi Rajan,

The surrogate key column is created in BODS (in query transform) and this column should be made primary key and use this in Key generation transform. It will create keys serially. it will not create random keys.

Please let me know if you need more info

Show 1 Share
10 |10000 characters needed characters left characters exceeded

Hello Ravi,

I added this surrogate column (Cust_gen) in query transform and same applied in TC and Key_gen and ran the job, but still found that its giving null values.

Also I mapped the input cust_id column with this surrogate key.

I have attached the screenshots of my job.

Please let me know where did I went wrong.

Thanks much for your help!! :)error-03.pngerror-04.pngerror-05.png

error-03.png (81.9 kB)
error-04.png (87.3 kB)
error-05.png (101.2 kB)
Ravi kiran Pagidi Jan 26, 2017 at 06:35 AM

Hi Rajan,

Pass 1 in the mapping for cust_gen in query transform. remove cust_gen from generated key column in Table comparison and also please picture of key_gen transform.

10 |10000 characters needed characters left characters exceeded