none
Incremental Load in Via CDC tables

    Question

  • Hi ,

    I am using SQL Server 2008 SSIS and SQL Server 2008 CDC features.

    my Source system will be a different machine.

    I do have seen lot of blogs and codplex package for CDC incremental load.

    I saw example from MSDN site as well.

    http://msdn.microsoft.com/en-us/library/bb895315(v=sql.100).aspx

    but if understand correctly that using LSN number of CDC database for detecting changes is not relaible.  i am facing same challange.

    See this post

    http://www.sqltact.com/2010/08/cdc-dbofnallchanges-frustrating.html

    I am facing same problem, it is not necessary that maximum and Min LSN number from CDC database can be pass on to cdc.fn_cdc_get_all_changes_. it will throw an error.

    also wehn you have mutiple tables to load it may be possible some of the tables have not captured any data as there was no change. so getting min and max LSN number from every table is again not a solution.

    What i would like to understand that what is way to reduce complexity of ETL and make this loading smother and faster.

    Else we have to create configuration table for having MAX-MIN LSN number for every table and also need to have conditional execution in SSIS to ensure those MAX and MIN LSN are not null and if NULL then dont pull data from Source.

    Please suggest?? if there are any better way...

    samples


    Gaurav Gupta http://sqlservermsbiblog.blogspot.com/ Please click "Mark as Answer" if this resolves your problem or "Vote as Helpful" if you find it helpful.

    Tuesday, July 02, 2013 1:59 AM

All replies

  • What is the source system?

    I think the issue is indeed source related and thus you need a change in approach?

    I suspect that you did not mark the initial load start, hence the issue detecting the changes, yes, it is perfectly reasonable for data not to change, but the CDC component is then aware.

    I suggest you look into an excellent post by Matt Masson on how to use CDC:

    http://www.mattmasson.com/2011/12/cdc-in-ssis-for-sql-server-2012-2/


    Arthur My Blog

    Wednesday, July 03, 2013 3:53 PM
    Moderator
  • Hi,

    Thanks for reply.

    I am not using SQL 2012, i am using SQL 2008 SSIS and CDC, where we dont have CDC component.


    Gaurav Gupta http://sqlservermsbiblog.blogspot.com/ Please click "Mark as Answer" if this resolves your problem or "Vote as Helpful" if you find it helpful.

    Wednesday, July 10, 2013 3:45 AM
  • And you should, SSIS 2012 CDC is a more robust implementation, if you want efficiency may CDC is not your 1st choice. 

    How about tran replication?


    Arthur My Blog

    Thursday, July 11, 2013 7:58 PM
    Moderator