utilizing CDC to capture changes

  • Siten0308

    SSCarpal Tunnel

    Points: 4334

    Hello,

    sorry I am fairly new to data warehousing, though I have some little experience and concepts, but looking into CDC, I know its good for capturing and auditing purposes, I was wondering if we can use that same captured information and create an SSIS package to get the rows that were changed, and apply it to the data warehouse fact tables that the data has been changed? is that possible? if not would it be best to just capture changes using SSIS Merge join?

    thanks in advance

  • Sue_H

    SSC Guru

    Points: 90700

    Yes, you can use CDC for incremental loading like that. The documentation has an overview of doing this:

    Change Data Capture (SSIS)

    There are quite a few articles with more details on doing this - search on CDC SSIS. Here is one with more details, screenshots on using this that may help you get started:

    SQL Server Integration Services SSIS CDC Tasks for Incremental Data Loading

    Sue

  • Siten0308

    SSCarpal Tunnel

    Points: 4334

    Awesome thank you Sue_H, one last question, in your experience, has CDC had overhead on the SQL server, of course it would depend on how many databases/tables and data size but just wondering if there was some or little overhead?

  • Sue_H

    SSC Guru

    Points: 90700

    It's one of those that, as you have guessed, really depends on the volume of changes being tracked. We had tested some of that by using replay traces and running those in test to get an idea of how much was being generated for the history tables. There may be some other ways but that's about all I can think of off hand to get an idea of what you might see.

    Sue

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

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