• Forgive me if I have oversimplified or misunderstood the issue, but this is how I understand it:

    You are getting a full backup of your DB nightly (test_stage) and you wish to capture all data modifications (new and updated records). Only these data modifications should be applied to your reporting database (test_dw). If this is what you are after then great - you don't really need to implement Change Data Capture to accomplish this.

    But if you need to capture the actual changes (Bill Smith was inserted, updated to Bill Smyth, updated to B Smyth, updated to Bill Smith, Bill Smith was deleted) then it will be a bit more complicated than I have the time/expertise/confidence to explain in a forum post.

    So I will hope the first scenario is correct and attempt to offer one possible solution to the issue (at a high level). If this is indeed what you need to accomplish and you need more detail please let me know.

    From 10,000 feet as I understand it, your existing package(s) restores the backup (test_stage) and "copies" (inserts?) the data from test_stage into test_cdc. I would suggest that you add Lookups to check if the data you are processing from test_stage exists in test_cdc before inserting. Then you can direct the matching rows to be processed one way (i.e. update/ignore - whatever your requirements dictate) and direct the non-matching rows (i.e. new records) to be inserted into test_cdc. Again - this is a 10,000 foot solution and if you believe it might satisfy your issue I'll be happy to get into the details with you.

    Hopefully this was helpful - if not, sorry! Best of luck!