Post reply

How to load only recent (Incremental) records into a table from a Function Source? - CDC

  • June 8, 2010 at 8:24 am

    #236263

    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

    #1181280

    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

    #1219636

    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