SSIS 2012 Change Data Capture (CDC) Control Task

Bradley Schacht

Bradley Schacht is a Cloud Solution Architect on the state and local government team with Microsoft based in Jacksonville, FL. He has co-authored 3 SQL Server books including "SQL Server 2014 Professional Administration". As a former consultant and trainer, he uses his experience on many parts of the Microsoft BI and data platform to help customers deliver the best possible solutions. Bradley frequently presents at community events around the country. He is a contributor to sites such as and an active member of the Jacksonville SQL Server User Group (JSSUG).

You may also like...

12 Responses

  1. SQL Nikon says:

    Enjoyed reading your post on CDC. Do you know if it’s possible to build a SSIS 2012 CDC flow with SQL Server 2008 R2 CDC enabled database and tables?

  2. Ketan Patil says:

    Hi Brad,

    Nice post there… Just a quick thing I wanna ask you. Do you recommend CDC (in SSIS 2012) to handle SCD type 2 changes?

    Please advise if you have any suggestions.

    Best Regards,

  3. sivakumar says:

    Great explination and really help full

  4. Naveem says:

    I need to implement the incremenatal load in sql server 2008. Is that possible to achive it .

  5. avix says:

    I’ve one confusion for Type 0 SCD and CDC is same kind of?

    • SCD is more of a concept (unless you mean the SCD wizard which is an SSIS component) and CDC is a technology that tracks what changes are made in the database. That being said a type 0 attribute is one that doesn’t change.

  6. Rucha says:

    These CDC components are provided by Atuunity? or Microsoft itself?

    • These are provided by Microsoft. They are not available from codeplex or anywhere outside the product though. They are built right into SSIS starting with the 2012 release. There is no way to get these components in 2008R2 or prior releases.

  7. Brad Sheridan says:

    Good morning Bradley – i came across your article this morning while researching something related to CDC in SSIS 2014. In #7 above you state “Finally choose the state name, if you are using all the defaults it will be CDC_State. There will be one record in the state table for each unique state. So if you want to maintain each table separately then you should create a different state for each table or group of tables.”

    My question is how to make the CDC_State name dynamic? I have 3 packages in a project, each package doing CDC on it’s own table. I would like to have 3 rows in my db0.cdc_states table accordingly. However, I can’t figure out how to parameterize the “State Name” in the CDC Control Task Editor. I’ve tried using package configurations with no luck yet.

    Brad Sheridan

  8. Sumit says:

    Hi –
    How can I implement CDC for multiple tables? I want to run all Data Flow Tasks for multiple tables in parallel. While doing initial load, do I need separate CDC Control Flow task for each table? Or, shall I just add multiple DFT’s between ‘CDC Control Flow – Start’ and ‘CDC Control Flow – End’.

    CDC State holds the LSN at database level or table level?
    Do I need distinct State Name for different tables if its in the same package with parallel DFT?

  9. Jana says:

    Hi Brad , do you know how it is with creating line lsn in sql server.
    We have cdc component Oracle-SQL server (2016 SP1), everything was ok. But last week was created in table cdc.lsn_time_mapping in column start_lsn out of order.
    start_lsn tran_begin_time
    0x0026DD1C523201000001 2017-07-10 11:16:46.010
    0x0054000100000000AF05 2017-07-10 11:16:46.010
    0x0026DD1C523601000001 2017-07-10 11:16:46.027

    How is it possible?
    Do you know ,how is it the solution ?



  1. November 27, 2014

    […] SSIS 2012 Change Data Capture (CDC) Control Task … – Change data capture was introduced in SQL Server 2008 and has been pretty popular ever since. The basic concept behind it is that all changes (inserts, updates …… […]

Leave a Reply