Change data capture - is it possible Updates getting applied out of sequence

  • I am using the CDC objects in SSIS to do replication between two servers

    I have a large table that does not have a Primary key so must be set to all.

    Occasionally when I do validation between the two, the record count is correct but values don't add up.

    I am doing a simple update from the CDC splitter to the destination table.

    Is it possible that the CDC splitter is not applying changes in proper sequence?

    There is very little documentation on these CDC objects  in SSIS

    both servers are fully patched to latest SQL 2016 Ver. and SSIS is running from a fully patched SQL 2017.


    Who is the expert on Change Data Capture?


  • Thanks for posting your issue and hopefully someone will answer soon.

    This is an automated bump to increase visibility of your question.

  • If you need to keep two replicated tables in sync and that is a requirement, it would help to have primary keys.  But the real issue is not that.  To fix this you need some form of 2 phase commit.  If one system gets an update or insert, both systems must reply and apply the change at the same time.  No commit till the 2 systems get it.  This is a performance loss but without auditing (which is way more expensive), there is no credible way to ensure this. Good luck.


  • This is an ETL to a datamart, so there is no Write activity on the destination other than the ETL This is a Change Data Capture using the SSIS objects  CDC Source and CDC Splitter. Though in this case the splitter has been bypassed.


    I have attached Screen shots of the SSIS package showing the process order

    and the SP that processes the deletes / inserts/ and updates.

    You must be logged in to view attached files.
  • Update fixed

    You must be logged in to view attached files.

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

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