Home Forums Data Warehousing Integration Services Using CDC Control Component for Initial Load with Read-Only Database RE: Using CDC Control Component for Initial Load with Read-Only Database

  • I think have a working solution now.

    I've built a table to capture the lsn.  In my initial load package, the first thing it does is to construct the CDC_state value from the lsn and write it to the table:

    state = 'TFEND/CS/' + CONVERT(VARCHAR, sys.fn_sqlvarbasetostr(sys.fn_cdc_get_max_lsn())) + '/TS/' + CAST(CAST(SYSUTCDATETIME() AS DATE) AS VARCHAR) + 'T' + CAST(CAST(SYSUTCDATETIME() AS TIME(7)) AS VARCHAR) + '/'

    So 0x0000035a0003f83a0004 becomes TFEND/CS/0x0000035a0003f83a0004/TS/2017-09-25T14:21:34.2973152/

    It also captures the end lsn after the initial load is complete, although I'm not sure that this is really necessary.

    When it comes to the incremental load, I'm just adding an extra step to assign the state value from the table to the CDC_state variable in the CDC control task (get processing range).  Once the control task has executed the CDC_State variable then becomes:

    TFSTART/CS/0x0000035A0003F83A0004/CE/0x0000035A0008CA0F0003/TS/2017-09-25T15:11:49.3382000/

    Once the incremental load is complete, and the processing range has been marked, the CDC_State variable becomes:

    {TFEND/CS/0x0000035A0008CA0F0003/TS/2017-09-25T15:12:58.9861921/}

    Which then gets assigned back to the state field in the table ready for the next incremental load.