最新項目稍有空隙,開始研究SQL Server 2012和2014的一些BI特性,參照(Matt)的一個示例,我們開始體驗SSIS中的CDC(Change Data Capture,變更數據捕獲)。
注:如果需要了解關於SQL Server 2008中的CDC,請看這裡http://www.cnblogs.com/downmoon/archive/2012/04/10/2439462.html),本文假定讀者對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(邀月),[email protected] -- ============================================= */ --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(邀月),[email protected] -- ============================================= */ 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(邀月),[email protected] -- ============================================= */ 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(邀月),[email protected] -- ============================================= */ SELECT TOP 0 * INTO DimCustomer_Destination FROM DimCustomer_CDC --select @@version; select * from DimCustomer_Destination;