DSXchange

DSXers,

I am loading a table with a unique (surrogate) key.

We have a hash file that maintains the the MAX() surrogate key for each table.

For all new records, the key values are generated in a link variable which uses the MAX() surrogate key from the hash and @INROWNUM:

SurrogateKey = HashMaxKeyLookup.MAX_SURRKEY + @INROWNUM

The issue is this expression is generating duplicates. The hash file has a single record (with value of 36,809). But I'm getting 55,115 as the SurrogateKey on two output records, which indicates that this happens on input row number 18,306 (55,115 - 36,809).

One thing I have noticed is that @OUTROWNUM is not repeating and creates unique keys. BUT, @OUTROWNUM is not available in Stage Variables.

Any ideas?

This is completely reproducible.

Hello Ryoung011,

first off, the good news is that the @INROWNUM is unique coming into your transform, so something else is going wrong. How are you doing the hash file lookup; are you doing it for each input row (not fast, but it works) ? If this is reproduceable then could you add another output link to a flat file which also contains the @INROWNUM as well as the hash lookup value and see if the error shows up there?

Hi,
Also is there another insert performed on that table while your job runs?
this is bound to mess your surogate key and cause duplicate keys.
it might even be a defunct still running from an aborted previous run of your own job.
IHTH,

Can it be that the surrogate key fn + inrownum gave this problem.

Eg.
Surr Key In Row Num Total
101 1 102
102 2 103
103 3 104
099 4 103
104 5 109

So please check the logic by passing these rows from the source back through the surrogate key transform.