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

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s