Change data capture-comparison of 2 records in after dataset

[dsuser7]

Hi,

I have a job comparing 2 datasets - before and after and supposed to capture the edits and inserts.

The after dataset has 2 records with same key column and the before dataset has 1 record with the key column.

Before ds:
K1,1,a

After ds:
K1,1,a
K1,2,b

Expected Result;
K1,1,a,no change
K1,2,b,edit

Actual result:
K1,1,a,no change
K1,2,b,insert

When the job runs, the 1st record in After ds gets compared with the one in Before ds and outputs nothing as there is no change.
But the 2nd record in the After ds comes out as an insert even though the key matches with the one in Before ds, just because the Before ds record is exhausted.

Is there a work around, so that both After records gets compared with the one in Before dataset.

Thanks.

[ArndW]

That is an interesting scenario, for purposes of the CDC the key is considered unique and as you noted the records are consumed.

You would need to ensure that the records are duplicated in both streams prior to the changed data capture; perhaps using a join or lookup to ensure a 1:1 matching of keys.

[nikhil_bhasin]

I guess, for Change capture stage, there should be unique keys in both before and after data. In my opinion DS won't do a row position by position comparison.

[dsuser7]

Thanks for your replies. Guess I will have to de dup the After dataset so it has just 1 record.