Change Data Capture (CDC) is a capability that SQL Server added starting with SQL Server 2008. It is a capability that other databases such as Oracle and 3rd party providers of general CDC behavior. In the past, many database administrators would use SQL Server’s transactional or snapshot replication to achieve a similar data refresh and data capture functionality in data warehouse systems.
CDC is very powerful and with the capabilities of CDC in SQL Server 2008, it is much easier to keep applications such as data warehouse and business intelligence systems refreshed with recent transactional system changes. If you were to use CDC in an SSIS pipeline, for example, you could query for the changed rows and perform transformations on that updated dataset.
I also started the blog off with CDC because I previously wrote an article for SearchSQLServer that I can just point you to for more insights here!
But there is also a much more lengthy, and very good, article in MS SQL Tips here that shows the process in its entirety as part of an SSIS package. Check it out.