Two ways of improving your ETL with SQL 2012 #3 Change data capture

In an earlier post we saw how the new project deployment functionality will make it much easier to deploy and keep track of versions of a SSIS package.

The other cool feature I will talk about is change data capture. How do you transfer data from your datasources (e.g. an ERP-system) to a staging database? Well, I see multiple ways, but the purpose is to transfer as few records as possible, and some of the methods are:

1) If a table has a last changed, then keep track of the “Last changed date” in the staging database, and then transfer all data since the last “Last Changed”.

2) If table as a rowID that are incremental, then transfer all new rows since the last transfer.

3) Transfer e.g. the last two days data and then work with them.

All methods has their ups and downs, but now we can utilize the Change Data Capture functionality, and I will show you how it works.

Basically, we must

1) add change data capture to a table

2) Then we must create the SSIS package

3) Run it initially the first time.

4) Setup the package to run continiously.


It is very easy, and I will try to give you a short demo on that now.



<– Project Deployment

0 replies

Leave a Reply

Want to join the discussion?
Feel free to contribute!

Leave a Reply

Your email address will not be published. Required fields are marked *