June 8, 2010 at 8:24 am
Hi,
I have a “Function” as a "OLE DB source". The “Function” is
DECLARE @from_lsn binary(10), @to_lsn binary(10);
SET @from_lsn = sys.fn_cdc_get_min_lsn('dbo_source_table');
SET @to_lsn = sys.fn_cdc_get_max_lsn();
SELECT * FROM cdc.fn_cdc_get_all_changes_dbo_source_table(@from_lsn, @to_lsn, 'all').
The output is something like this
__$start_lsn __$seqval __$operation __$update_mask ID Name
0x0000001B000001C50013 0x0000001B000001C50012 2 0x03 1 Gouri
0x0000001B000001C90003 0x0000001B000001C90002 2 0x03 2 Smita
0x0000001B000001CA0003 0x0000001B000001CA0002 2 0x03 3 Subodh
0x0000001B000001CB0003 0x0000001B000001CB0002 2 0x03 4 Sarita
0x0000001B000001CE0004 0x0000001B000001CE0002 1 0x03 4 Sarita
0x0000001B000001CF0004 0x0000001B000001CF0002 4 0x02 1 Gouri S
0x0000001B000001D00003 0x0000001B000001D00002 2 0x03 5 Ramya
0x0000001D0000007A0004 0x0000001D0000007A0002 4 0x02 1 Gouri T
0x0000001D0000007B0003 0x0000001D0000007B0002 2 0x03 6 Roja
The “OLE DB Destination” is a table. It just gets the records from the “Source”. No transformation in between.
Now, the problem is, whenever I run the package, it loads all the records in to the “Destination” table.
Ex. If I run the first time, all the 9 records are inserted into the Decstination table. I did some 1 insertion, 1 updation and 1 deletion in the source. Using CDC, the Source function now retrieves ( 9+3=) 13 records. So, when I run the package, instead of loading only the new 3 records, all the 13 records are getting loaded into the Destination which I do not want.
Can you guys please help me to solve this?
June 16, 2010 at 2:37 pm
use select SELECT * FROM cdc.fn_cdc_get_net_changes_dbo_source_table(@from_lsn, @to_lsn, 'all').
instead of SELECT * FROM cdc.fn_cdc_get_all_changes_dbo_source_table(@from_lsn, @to_lsn, 'all').
September 11, 2010 at 9:35 am
You need to save th @to_lsn and use that as the new @from_lsn the next time you call the functions. This moves up the bottom water mark to automatically skip the already returned rows.
Most common technique is to store the From and To lsn values in a CDC job history tracking table.
--Chris Skorlinski
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply