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
Yes, you can use CDC for incremental loading like that. The documentation has an overview of doing this:
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
January 30, 2020 at 2:44 pm
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?
January 30, 2020 at 2:52 pm
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 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply