July 15, 2019 at 5:56 pm
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?
July 16, 2019 at 6:10 pm
Thanks for posting your issue and hopefully someone will answer soon.
This is an automated bump to increase visibility of your question.
July 18, 2019 at 12:37 pm
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.
July 18, 2019 at 7:49 pm
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.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply