SSIS 2012異動資料擷取(下)
在上期專欄中，我們透過「CDC控制工作」與「資料流程工作」初始化了來源和目的資料表，讓目的資料表的內容與來源資料表一致，並記錄了所處裡記錄的LSN。在本期專欄中，繼續透過「CDC控制工作」提供的不同作業選項，並藉由SSIS 2012在資料流程內新增的「CDC來源」與「CDC分隔器」元件，持續地同步兩個資料表。

首先再建立一個新的SSIS封裝，這個封裝可重複執行多次，以遞增的方式將變動資料從來源資料表帶到目的資料表。其最終設計結果如圖1所示：


▲ 圖1 可重複執行的封裝，持續將來源資料表的變更同步到目的資料表。


依然是加入「CDC控制工作」到封裝設計環境中，其設定方式與上篇專欄相似。點選最上方「SQL Server CDC資料庫ADO.NET連結管理員」下拉選單旁的「新增」按鈕，在接下來的「設定ADO.NET連接管理員」對話窗中，選到上篇專欄在SQL Server建立的CDCTest測試資料庫。於「CDC控制工作編輯器」對話窗的「CDC控制作業」下拉選單中，選擇「取得處理中的範圍」選項，如圖2所示：


▲ 圖2 透過取得處理中的範圍作業選項，讓「CDC控制作業」取得此次同步要處理的LSN範圍，並放入封裝變數。


「CDC控制作業」選項設定是圖1中兩個「CDC控制工作」與上篇專欄使用的兩個「CDC控制工作」最大的差異，分別說明用來持續同步的「CDC控制作業」選項如下：
●取得處理中的範圍(GetProcessingRange)：在叫用內含「CDC 來源」的資料流程之前使用。使用此作業時，它會建立「CDC 來源」所讀取記錄的LSN範圍。此範圍會儲存在資料流程處理期間，「CDC 來源」所使用的封裝變數中。
●標示已處理的範圍(MarkProcessedRange)：在每次CDC資料流程順利完成之後執行此作業，以便記錄 CDC 執行期間完整處理的最後一個LSN。下次執行「取得處理中的範圍」時，這個位置就是下一個處理範圍的開頭。

圖2中，「CDC控制作業」後的設定與上篇專欄相同，在此不再贅言。建立完執行「取得處理中的範圍」作業之「CDC控制工作」後，在封裝設計環境加入「資料流程工作」，該「資料流程工作」設計完成的內容如圖3所示：


▲ 圖3 在資料流程中，透過「CDC 來源」和「CDC 分隔器」取得變更差異資料。


在資料流程中，首先加入「CDC 來源」，它會從SQL Server啟動CDC追蹤變更的資料表中，讀取先前「CDC控制工作」所設定範圍內的變更資料，進而傳遞至其他的SSIS資料元件。「CDC 來源」傳回的資料與SQL Server資料庫內提供的CDC函數傳回的資料相同。

[註]針對每個啟動CDC功能的資料表，SQL Server自動建立的兩個函數：「cdc.fn_cdc_get_all_changes_<擷取執行個體名稱>」或「cdc.fn_cdc_get_net_changes_<擷取執行個體名稱>(如果針對要追蹤的資料表有啟動supports_net_changes設定，才有這個函數)」。

「CDC 來源」從封裝變數所讀取的資料範圍稱為CDC處理範圍，由當下資料流程啟動之前執行的「CDC 控制工作」所決定。「CDC 控制工作」會存取相關的資料表，讀出先前執行過同步的LSN與當下累積變動之最大LSN組成的狀態，放入封裝變數值。

「CDC 來源」會使用資料庫資料表、檢視或SQL陳述式，從資料庫中擷取資料。其設定畫面如圖4所示：


▲ 圖4 透過「CDC 來源」讀取某個資料表特定範圍內的變更記錄。


圖4上方的「ADO.NET 連接管理員」，需使用可以存取到SQL Server執行個體內已啟動CDC功能的資料庫，可以透過旁邊的「新增」按鈕直接建立。接著在「啟用CDC的資料表」指定資料庫內有啟用CDC功能的資料表名稱。在「擷取執行個體」下拉選單會自動提供所選資料表的擷取執行個體名稱，這在你啟動某個資料表的CDC功能時會自動賦予。若要觀察該擷取執行個體名稱，可以透過sys.sp_cdc_help_change_data_capture系統預存程序，搭配此處的範例，其語法如下：

EXECUTE sys.sp_cdc_help_change_data_capture
@source_schema = N'dbo',
@source_name = N'DimCustomer_CDC';

接著選擇「CDC處理模式」，決定要所有變更的細節還是淨值。其選項包括：
●全部：傳回目前CDC範圍中的變更，不含更新之前的記錄值。
●全部使用舊值：傳回目前CDC處理範圍中的變更，包括記錄更新之前的舊值。每個更新作業都有兩筆資料列：一筆更新前的值，另一筆則是更新後的值。
●淨值：針對目前CDC處理範圍中，每筆有修改的來源資料列傳回一項變更。如果同一筆來源資料列在此範圍內更新了許多次，就會結合其變更結果(例如，新增+修改會產生為單一新增，而修改+刪除則產生為單一刪除)。
●使用更新遮罩淨值：與一般的「淨值」模式很相似，但是它還加入了名稱模式為「__$<欄位名稱>__Changed」的布林資料行，表示目前變更資料列中的變更資料行。如圖5所示：


▲ 圖5 透過使用更新遮罩淨值模式，會增加標示某資料行是否被更新的新資料行。


●使用合併的淨值：與一般的淨值模式很相似，但是插入和更新作業會合併成單一合併作業。
在此練習中，採用「淨值」。接下來設定做為決定 CDC 處理範圍之依據的CDC狀態封裝變數名稱，也就是先前在設定「CDC控制作業」時所建置的CDC_State封裝變數。「CDC來源」不會修改該變數。最後，不用勾選圖4最下方的「包含重新處理指標資料行」。

[註] 選取該核取方塊會在輸出增加名為「__$reprocessing」的特殊資料行。當CDC處理範圍與初始處理範圍 (對應至初始載入週期之 LSN 的範圍) 重疊，或者上一次執行發生錯誤之後重新處理CDC 處理範圍時，這個資料行的值就是true。
這個指標資料行可讓SSIS開發人員在重新處理變更時以不同的方式處理錯誤(例如，刪除不存在的資料列以及忽略在重複索引鍵上失敗的插入等動作)。

設定完「CDC來源」後(參照圖2)，筆者故意插入一個「CDC分隔器」元件，讓你了解「CDC分隔器」元件的用途，但依這個範例封裝的設計，其實可以不用「CDC分隔器」元件。將代表資料流的藍色箭頭從「CDC來源」拉到「CDC分隔器」元件。

「CDC 分隔器」會將「CDC 來源」資料流程中變更資料列的單一流程分成「插入」、「更新」和「刪除」等不同的資料流程。資料流程是根據SQL Server變更資料表中的資料行「_$operation」 之值決定，其資料行值定義如下：


▲ 表1 「CDC 分隔器」依據「_$operation」資料行將不同的記錄分流


在此範例中，「CDC 來源」採用「淨值」處理模式，透過「CDC 分隔器」產生不同輸出，以平行方式處理它們。在此僅是示範「CDC 分隔器」所作的分流，但無論走那一條輸出，筆者都將輸出的資料放到暫存資料表中，該資料表的定義如下：

程式碼範例1：建立暫存資料表，儲存「CDC 來源」讀取出的記錄
CREATE TABLE [tblCDC_Stage] (
[__$start_lsn] binary(10),
[__$operation] int,
[__$update_mask] binary(128),
[Phone] nvarchar(24),
[CustomerID] nvarchar(5),
[CompanyName] nvarchar(40),
[ContactName] nvarchar(30),
[ContactTitle] nvarchar(30),
[Address] nvarchar(60),
[City] nvarchar(15),
[Region] nvarchar(15),
[PostalCode] nvarchar(10),
[Country] nvarchar(15),
[Fax] nvarchar(24)
)

在資料流程最後，因為是在同一個SQL Server執行個體，故透過「SQL Server目的地」批次載入所有的資料。

而後回到「控制流程」增加一個「CDC控制工作」，其設定與圖2相同，僅是「CDC控制作業」選項設定為「標示已處理的範圍」，以記載完成的LSN到資料表中，供下次繼續同步時的起點參考。

最後加入一個「執行SQL工作」，設定「Conneciton」屬性連接到此次測試用的資料庫，其「SQLStatement」屬性設定如下：

程式碼範例2：將中介資料表的內容合併到待同步的目標資料表中

--先刪掉全部，再新增 Insert、Update 的記錄
DELETE [dbo].[DimCustomer_Destination] FROM [dbo].[DimCustomer_Destination] d
JOIN [dbo].[tblCDC_Stage] s ON d.CustomerID=s.CustomerID ;

--將變更後的資料新增到目的資料表中
INSERT [dbo].[DimCustomer_Destination]
SELECT CustomerID, CompanyName, ContactName, ContactTitle, Address,
City, Region, PostalCode, Country, Phone, Fax
FROM [dbo].[tblCDC_Stage] s WHERE s.__$operation in(2,4); --2：新增, 4：修改後的資料內容

--更新完畢後，清掉中介資料表的內容
TRUNCATE TABLE [dbo].[tblCDC_Stage]

程式碼範例2中，把目的資料表中與佔存資料表相同鍵值(CustomerID)的記錄都刪掉，而後把來源端新增與修改後的記錄通通新增到目的端資料表。至此完成遞增同步兩個資料表的封裝設計。而後透過SQL Server Management Studio對來源資料表執行變更記錄的語法如下：

程式碼範例3：對來源資料表進行新增、修改和刪除

-- 測試遞增資料同步
-- 對原資料表新增、修改、刪除記錄
INSERT INTO DimCustomer_CDC
SELECT *
FROM [Northwind].[dbo].[Customers]
WHERE CustomerID >= 'K%' AND CustomerID < 'Q%'
GO
-- 修改記錄
UPDATE DimCustomer_CDC
SET CompanyName=CompanyName + '1'
GO

DELETE TOP(10) DimCustomer_CDC

執行程式碼範例3的三句T-SQL語句後，其受影響的記錄筆數統計如下：

(22 個資料列受到影響)

(60 個資料列受到影響)

(10 個資料列受到影響)

當要測試上述的封裝時，可以滑鼠右鍵點選代表資料流程的藍色箭頭線段，啟動「資料檢視器」。若「CDC來源」讀取到變更資料，可直接觀察變更資料的內容，如圖6所示：


▲ 圖6 透過「資料檢視器」觀察「CDC 來源」讀取到的變更資料記錄。


若第一次執行封裝發現沒有變更記錄，代表透過SQL Agent服務啟動的Log Reader代理程式尚未啟動讀取交易記錄，你可以稍待一會，等變更資料讀入System Table，再執行一遍這個封裝。在資料流程應可看到如下的執行結果：


▲ 圖7 各資料流取得不同變化的記錄筆數。


參考程式碼範例3所傳回的受影響記錄筆數，對比圖7的新增、修改和刪除資料流，可以發現因為採用淨值，所以22筆的新增記錄雖然其後都做了修改，但最終等於新增。另外10筆記錄先做了修改，而後又被刪除，所以等同刪除10筆記錄。所以，60筆修改的記錄在淨值變化下，在這一次的CDC處理範圍中，僅有28筆記錄是實質修改。

若封裝正常執行完畢，且同步了變更記錄，可以透過以下的語法簡單比對兩個資料表的內容是否一致：

--呼叫遞增同步的封裝後，檢視結果
SELECT ●FROM DimCustomer_CDC Order By CustomerID
SELECT ●FROM [dbo].[DimCustomer_Destination] Order By CustomerID

你可以持續執行變更的語法，例如：

-- 修改記錄
UPDATE DimCustomer_CDC SET CompanyName=CompanyName + '1'
GO
DELETE TOP(10) DimCustomer_CDC

而後再搭配執行封裝數次，同時觀察是否變異資料都同步到目標資料表中。

在下列情況下，CDC控制工作可能會報告錯誤：
●它無法讀取CDC永續性狀態或永續性狀態更新失敗。
●它無法從來源資料庫讀取目前的LSN資訊。
●CDC狀態讀取不一致。

在上述情況下，「CDC 控制工作」都會報告錯誤，而 SSIS 則會以處理控制流程錯誤的方式處理。
另外，在未呼叫「標示已處理的範圍」前，直接在「取得處理中的範圍」作業之後叫用另一個「取得處理中的範圍作業」時，「CDC 控制工作」可能也會警告。這種情況也可能發生在上次執行失敗，或者另一個 CDC 封裝正以相同的「CDC 狀態名稱」執行。

最後補充一點，依筆者透過SSIS設計資料整合(Extract Transform Load ETL)流程的經驗，其實資料流程最好在SQL Server透過預存程序搭配連結伺服器執行，既有彈性又有效率。SSIS僅處理「控制流程」，也就是把執行邏輯的先後順序畫出來，再透過「執行SQL工作」要SQL Server的資料庫引擎處理資料。

換句話說，若要執行這兩期文章所介紹的CDC資料同步，筆者會偏向用預存程序做完，而非採用此處所介紹的工作和元件。那何時會採用這兩篇文章所介紹的功能呢？除非需要大量.NET程式客制化的資料整合，或是在SQL Server的預存程序或連結伺服器有所限制的企業環境內。