I am using a Change Capture stage to figure the changes between a Before dataset and After dataset.
For clarity purpose, let me show you the layout of the before and after datasets. (Both of them have the same column list, as they are extracts of the same table from different environments)
Before Dataset
Key-ColValue-Col1Value-Col2
K1; 1; A
K2; 2; B
K3 ;3; C
After Dataset
Key-Col Value-Col1 Value-Col2
K1;1;A
K2;2;F
K5;5;E
Say that I gave change codes for copy (Change-Code = 0) and inserts (Change-Code = 1) and deletes (Change Code = 2) and updates (Change-Code =3).
As you can see above, record with key K1 did not change, K2 had a change in Value-Col2, K3 got deleted and K5 is a new insert in the 'After' dataset. So I expected my change dataset to be as follows:
Change Dataset (Expected)
Key-Col:Value-Col1:Value-Col2:Change_Code
K1;1;A;0
K2;2;F;3
K3;NULL;NULL;2
K5;5;E;1
Change Dataset (Actual)
Key-Col:Value-Col1:Value-Col2:Change_Code
K1;1;A;0
K2;2;F;3
K3;3;C;2
K5;5;E;1
The problem I notice is that the records with a Delete change-code (2) have the values from 'Before' dataset. Since change capture stage outputs the 'After' data set values (I say this because in the in the Output tab I see the columns prefixed as 'After.'), I expected it to carry no information for the value columns 1 & 2 and just have the Key colum as 'K3' (See Highlighted row on Change Dataset (Expected) table.
Why would this happen? Can I get a change dataset as I exepcted above?
Additional Notes:
1. I have removed RPC from all links (set at a job level)
2. The inputs ot the Change Capture stage are Hash partitioned and sorted on the Key column
3. I specified Change Mode as'Explicit Keys & Values'
4. I set the Drop Output for Copy, Insert, Delete and Edit as False
I am trying to use the change dataset and merge it with the before dataset to create a change detect file which carries the accurate before and after information for archival and audit purposes. But because the records with Change-Code = 2 (Delete) retain the before column values, the change detect file would have the same information in 'Before' and 'After' columns. I hope I made my question clear!
Apologies for the formatting...I don't seem to get it right..
![Sad :-(](./images/smilies/icon_sad.gif)
Thank you folks!
N