How to use data change capture in SSIS 2012

With a few gaps in the latest projects, starting with some of the bi features of SQL Server 2012 and 2014, referring to an example from Matt, we started to experience the CDC in SSIS (change data Capture).

Note: If you need to know about the CDC in SQL Server 2008, see here http://www.cnblogs.com/downmoon/archive/2012/04/10/2439462.html), This article assumes that the reader has an understanding of how the CDC works. ^_^.

We complete the example in three steps:

1, prepare the basic data;

2, the design of an initial package;

3, on the basis of 2 design an incremental package.

First, complete the following ready installation:

(1) Visual Studio 2012 or Visual Studio Shell (Isolated) Redistributable Package

http://www.microsoft.com/en-us/download/details.aspx?id=30678

http://www.microsoft.com/en-us/download/details.aspx?id=30670

(2) SQL Server Data tools-business Intelligence for Visual Studio 2012

http://www.microsoft.com/zh-cn/download/details.aspx?id=36843

(2) SQL Server 2012 Enterprise Edition or Development edition

http://www.microsoft.com/en-us/download/details.aspx?id=29066

(3) Sample Database AdventureWorksDW2012 (This article must, if you do not need to build the table)

http://msftdbprodsamples.codeplex.com/releases/view/55330

Okay, first step:

/*-
=============================================-
Create test database and datasheet, with AdventureWorksDW2012 sample database
--- Generate by Downmoon (invitation month), 3w@live.cn
-=============================================
* * *
--create Database cdctest
--go
--use [cdctest]
--go--select
    
* into DIMCUSTOMER_CDC--from
[ ADVENTUREWORKSDW2012]. [dbo]. [DimCustomer]
--where Customerkey < 11500;
    
--select * from DIMCUSTOMER_CDC;

/*-=============================================-Enables database-level CDC, only valid for enterprise and Development editions---Generate by Downmoon (invitation month),  3w@live.cn-============================================= * * [cdctest] go EXEC sys.sp_cdc_enable_db Go-- Add a primary key to the DIMCUSTOMER_CDC table so we can enable support for net changes IF not EXISTS (SELECT * from sys. Indexes WHERE object_id = object_id (N ' [dbo].[ DIMCUSTOMER_CDC] and name = N ' PK_DIMCUSTOMER_CDC ') ALTER TABLE [dbo]. [DIMCUSTOMER_CDC] ADD CONSTRAINT [PK_DIMCUSTOMER_CDC] PRIMARY KEY CLUSTERED ([Customerkey] ASC) Go/*-======================= ======================--Enable table level CDC---Generate by Downmoon (invitation month), 3w@live.cn-======================================== =====/EXEC sys.sp_cdc_enable_table @source_schema = n ' dbo ', @source_name = N ' dimcustomer_cdc ', @role_name = N ' cdc_admi n ', @supports_net_changes = 1 Go 

/*-
=============================================-
creates a target table with the same table structure as the source table
-Note that in a production environment, Can be a different instance or server, in this case, for convenience, demonstrate
---Generate by Downmoon (invitation month) in the same database instance of the same database, 3w@live.cn
-===================== ========================
/
SELECT top 0 * to dimcustomer_destination from
DIMCUSTOMER_CDC
-- SELECT @ @version;
SELECT * from Dimcustomer_destination;