How to use data change capture in SSIS 2012

Source: Internet
Author: User
Tags create database visual studio ssis

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;

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.