Post reply

Using cdc LSN for SSIS multiple table export without database snapshot

  • September 26, 2013 at 8:24 am

    #278383

    Hi,

    I have a range of tables enabled with CDC.

    At a set point I want to export all the net changes from these tables.

    I am using SSIS, and the first thing I need to do is decide on the start LSN and the end LSN.

    This is easy if I have a database snapshot. However, say that I do not.

    How do I ensure that I select the start LSN for each of the 5 tables so that the data exported from

    all of these 5 tables reconcile back to each other like a snapshot in time?

    Do I record the time when the SSIS process is kicked off, and then use this time for each of the

    5 table exports, to find the LSN? Would this remove any risk of the 5 tables being inconsistent?

    The start LSN is simpler since it will be the previous run's end LSN.

    Thanks

  • September 26, 2013 at 8:48 am

    #1653606

    You can use the cdc.lsn_time_mapping table to convert LSN to transaction time. If you then transfer only cdc records which changed during a fixed period, like the last day, this should be enough. In case you want to export all changes since you last run the SSIS package, it's probably best to create a separate table which records the last runtime or the last LSN exported.

    [font="Verdana"]Markus Bohse[/font]

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply