最新项目稍有空隙，开始研究SQL Server 2012和2014的一些BI特性，参照(Matt)的一个示例，我们开始体验SSIS中的CDC（Change Data Capture,变更数据捕获）。
注：如果需要了解关于SQL Server 2008中的CDC，请看这里http://blog.csdn.net/downmoon/article/details/7443627），本文假定读者对CDC的工作方式已有所了解。^_^。
我们分三步完成实例：
1、准备基础数据；
2、设计一个初始包；
3、在2的基础上设计一个增量包。
首先请完成以下准备安装：
（1）Visual studio 2012或Visual Studio 2012 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企业版或开发版
http://www.microsoft.com/en-us/download/details.aspx id=29066
（3）示例数据库AdventureWorksDW2012（本文必须，如果自建表则不必）
http://msftdbprodsamples.codeplex.com/releases/view/55330
好了，开始第一步：
/*
-- =============================================
-- 创建测试数据库及数据表，借助AdventureWorksDW2012示例数据库
---Generate By downmoon(邀月),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;
/* -- ============================================= -- 启用数据库级别CDC，只对企业版和开发版有效 ---Generate By downmoon(邀月),3w@live.cn -- ============================================= */ USE [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 /* -- ============================================= -- 启用表级别CDC ---Generate By downmoon(邀月),3w@live.cn -- ============================================= */ EXEC sys.sp_cdc_enable_table @source_schema = N'dbo', @source_name = N'DimCustomer_CDC', @role_name = N'cdc_admin', @supports_net_changes = 1 GO
/* -- ============================================= -- 创建一个目标表，与源表(Source)有相同的表结构 --注意，在生产环境中，完全可以是不同的实例或服务器，本例为了方便，在同一个数据库实例的同一个数据库中演示 ---Generate By downmoon(邀月),3w@live.cn -- ============================================= */ SELECT TOP 0 * INTO DimCustomer_Destination FROM DimCustomer_CDC --select @@version; select * from DimCustomer_Destination;
第二步：创建初始包
-- =============================================
-- 我们使用两个包来完成示例，一个初始包完成数据的初始加载，一个增量包完成数据的变更捕获
---Generate By downmoon(邀月),3w@live.cn
-- =============================================
初始包包含如下逻辑：
新建一个SSIS项目，创建一个包“Initial Load”，如下图：
新建两个CDC Control Task,分别命名为“CDC Control Task Start”和“CDC Control Task End”，分别对应属性为“Mark initial load start”和""Mark initial load end"
连接管理器均为ADO.NET方式，其他属性如下图：
中间加入一个“Data Flow Task”,属性默认。
此时，运行包，可见CDC_States有初始标记。
第三步：创建增量包
增量包包含如下逻辑：
在项目中创建一个新包，命名为“Incremental Load”
在包的"Control Flow"视图中，自上而下分别手动6个Task，顺序如下图，除去上面用到的三个Task,其余均为Execute SQL Task
注意：CDC Control Task End的CDC运算符为MARK Process Range，CDC Control Task Start的CDC运算符为Get Process Range
其余4个Execute SQL Task的SQL语句如下：
--Create stage Tables
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID