(N'[dbo].[stg_DimCustomer_UPDATES]') AND type in (N'U'))
BEGIN
SELECT TOP 0 * INTO stg_DimCustomer_UPDATES
FROM DimCustomer_Destination
END
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[stg_DimCustomer_DELETES]') AND type in (N'U'))
BEGIN
SELECT TOP 0 * INTO stg_DimCustomer_DELETES
FROM DimCustomer_Destination
END -- batch update UPDATE dest SET dest.FirstName = stg.FirstName, dest.MiddleName = stg.MiddleName, dest.LastName = stg.LastName, dest.YearlyIncome = stg.YearlyIncome FROM [DimCustomer_Destination] dest, [stg_DimCustomer_UPDATES] stg WHERE stg.[CustomerKey] = dest.[CustomerKey]
-- batch delete DELETE FROM [DimCustomer_Destination] WHERE[CustomerKey] IN ( SELECT [CustomerKey] FROM [dbo].[stg_DimCustomer_DELETES] )
-- truncate table truncate table [dbo].[stg_DimCustomer_DELETES] truncate table [dbo].[stg_DimCustomer_UPDATES]
最关键的一步，选中CDC Control Task Start，并切换到Data Flow，自上而下分别拖动CDC Source，CDC Splitter Transformer，三个ADO.NET Destination，如下图：
其中三个的目标表分别为：[DimCustomer_Destination],stg_DimCustomer_DELETES,stg_DimCustomer_UPDATES。
而CDC Source的连接管理器属性如下图：
此时，可运行增量包，但我们不会看到任何运行结果，因为此时我们还没有进行数据的Insert或Update操作。
下来我们提供一个脚本，测试下效果：
-- =============================================
-- 更新一些数据,以显示SSIS 2012中CDC的效果
---Generate By downmoon(邀月),3w@live.cn
-- =============================================
USE [CDCTest]
GO
-- Transfer the remaining customer rows
SET IDENTITY_INSERT DimCustomer_CDC ON
INSERT INTO DimCustomer_CDC
(
CustomerKey, GeographyKey, CustomerAlternateKey, Title, FirstName,
MiddleName, LastName, NameStyle, BirthDate, MaritalStatus,
Suffix, Gender, EmailAddress, YearlyIncome, TotalChildren,
NumberChildrenAtHome, EnglishEducation, SpanishEducation,
FrenchEducation, EnglishOccupation, SpanishOccupation,
FrenchOccupation, HouseOwnerFlag, NumberCarsOwned, AddressLine1,
AddressLine2, Phone, DateFirstPurchase, CommuteDistance
)
SELECT CustomerKey, GeographyKey, CustomerAlternateKey, Title, FirstName,
MiddleName, LastName, NameStyle, BirthDate, MaritalStatus,
Suffix, Gender, EmailAddress, YearlyIncome, TotalChildren,
NumberChildrenAtHome, EnglishEducation, SpanishEducation,
FrenchEducation, EnglishOccupation, SpanishOccupation,
FrenchOccupation, HouseOwnerFlag, NumberCarsOwned, AddressLine1,
AddressLine2, Phone, DateFirstPurchase, CommuteDistance
FROM [AdventureWorksDW2012].[dbo].[DimCustomer]
WHERE CustomerKey =11502
SET IDENTITY_INSERT DimCustomer_CDC OFF
GO
-- give 10 people a raise
UPDATE DimCustomer_CDC
SET
YearlyIncome = YearlyIncome + 10
WHERE
CustomerKey >= 11000 AND CustomerKey <= 11010
GO
此时，我们可以看到变更捕获的结果：
如果您觉得还不够直观，请"Enable Data Viewer",
至此，一个SSIS 2012中CDC的实例演示结束，如果还有进一步的研究，请移驾MSDN，下面有链接。本文也提供示例项目包，以作研究之用。
项目文件下载1，项目文件下载2