建立函數以擷取變更資料

完成執行累加式異動資料載入之 Integration Services 封裝的控制流程後，下一個工作是建立可擷取異動資料的資料表值函數。第一次累加式載入前，您僅需要建立一次這個函數。

[!附註]

建立可擷取變更資料的函數是建立執行累加式變更資料載入之封裝程序的第二個步驟。如需建立此封裝之完整程序的描述，請參閱＜利用異動資料擷取改善累加式載入＞。

若要查看可示範封裝內異動資料擷取之使用的完整端對端範例，請參閱 Codeplex 上的「指定之間隔的異動資料擷取封裝」範例和「自上次要求後的異動資料擷取封裝」範例 (英文)。

異動資料擷取函數的設計考量

若要擷取變更資料，封裝之資料流程中的來源元件會呼叫下列其中一個異動資料擷取查詢函數：

• cdc.fn_cdc_get_net_changes_<capture_instance>—對於此查詢，針對每個更新傳回的單一資料列包含每個變更資料列的最終狀態。在大部分的情況下，及僅需要淨變更查詢所傳回的資料。如需詳細資訊，請參閱＜cdc.fn_cdc_get_net_changes_<capture_instance> (Transact-SQL)＞。

• cdc.fn_cdc_get_all_changes_<capture_instance>—此查詢會傳回擷取間隔期間，在每個資料列中發生的所有變更。如需詳細資訊，請參閱＜cdc.fn_cdc_get_all_changes_<capture_instance> (Transact-SQL)＞。

接著，來源元件會接收函數所傳回的結果，並將這些結果傳遞到下游的轉換和目的地，以便將變更資料套用到最終的目的地。

不過，Integration Services 來源元件無法直接呼叫這些異動資料擷取函數。Integration Services 來源元件需要查詢所傳回之資料行的相關中繼資料。異動資料擷取函數不會定義其輸出資料表的資料行。因此，這些函數不會針對 Integration Services 來源元件傳回足夠的中繼資料。

但是，由於資料表值包裝函式會在其 RETURNS 子句中，明確定義其輸出資料表的資料行，因此您可以使用此種函數。此資料行的明確定義會提供 Integration Services 來源元件所需的中繼資料。您必須針對每個您要擷取其變更資料的資料表，建立這個函數。

您有兩個選擇，可以建立呼叫異動資料擷取查詢函數的資料表值包裝函式：

• 您可以呼叫 sys.sp_cdc_generate_wrapper_function 系統預存程序來建立資料表值函數。

• 您可以使用本主題中的指導方針與範例，撰寫自己的資料表值函數。

呼叫預存程序來建立資料表值函數

建立您需要之資料表值函數最快速而且簡單的方式，就是呼叫 sys.sp_cdc_generate_wrapper_function 系統預存程序。此預存程序會產生指令碼來建立特別設計的包裝函式，以符合 Integration Services 來源元件的需求。

重要事項
sys.sp_cdc_generate_wrapper_function 系統預存程序不會直接建立包裝函式。但是，預存程序會針對包裝函式產生 CREATE 指令碼。開發人員必須先執行預存程序所產生的 CREATE 指令碼，累加式載入封裝才能呼叫包裝函式。

若要了解如何使用此系統預存程序，您應該先了解程序的用途、程序所產生的指令碼，以及指令碼所建立的包裝函式。

了解與使用預存程序

sys.sp_cdc_generate_wrapper_function 系統預存程序會產生指令碼來建立 Integration Services 封裝使用的包裝函式。

此處為預存程序定義的前幾行：

CREATE PROCEDURE sys.sp_cdc_generate_wrapper_function

(

@capture\_instance sysname = null

@closed\_high\_end\_point bit = 1,

@column\_list = null,

@update\_flag\_list = null

)

預存程序的所有參數都是選擇性的。如果您在不提供任何參數值的情況下呼叫預存程序，預存程序就會為您可存取的所有擷取執行個體建立包裝函式。

[!附註]

如需有關此預存程序之語法及其參數的詳細資訊，請參閱＜sys.sp_cdc_generate_wrapper_function (Transact-SQL)＞。

預存程序永遠會產生一個包裝函式來傳回每個擷取執行個體的所有變更。如果 @supports\_net\_changes 參數在建立擷取執行個體時設定，預存程序也會產生一個包裝函式來傳回每個適用之擷取執行個體的淨變更。

預存程序會傳回包含兩個資料行的結果集：

• 預存程序產生之包裝函式的名稱。此預存程序會從擷取執行個體的名稱衍生函數名稱 (函數名稱為 'fn_all_changes_'，後面接著擷取執行個體名稱。用於淨變更函數 (如果有建立) 的前置詞為 'fn_net_changes_')。

• 適用於包裝函式的 CREATE 陳述式

了解與使用預存程序所建立的指令碼

開發人員通常會使用 INSERT...EXEC 陳述式來呼叫 sys.sp_cdc_generate_wrapper_function 預存程序，並將預存程序所建立的指令碼儲存到暫存資料表中。接著，您可以個別選取並執行每個指令碼，以建立對應的包裝函式。不過，開發人員也可以使用一組 SQL 命令來執行所有 CREATE 指令碼，如下列範例程式碼所示：

create table #wrapper_functions
      (function_name sysname, create_stmt nvarchar(max))
insert into #wrapper_functions
exec sys.sp_cdc_generate_wrapper_function

declare @stmt nvarchar(max)
declare #hfunctions cursor local fast_forward for 
      select create_stmt from #wrapper_functions
open #hfunctions
fetch #hfunctions into @stmt
while (@@fetch_status <> -1)
begin
      exec sp_executesql @stmt
      fetch #hfunctions into @stmt
end
close #hfunctions
deallocate #hfunctions

了解與使用預存程序所建立的函數

若要有系統地查核已擷取之異動資料的時間表，所產生的包裝函式會預期適用於一個間隔的 @end\_time 參數將會是適用於後續間隔的 @start\_time 參數。遵循此慣例時，所產生的包裝函式可以進行下列工作：

• 將日期/時間值對應到內部使用的 LSN 值。

• 請確定沒有資料遺失或重複。

為讓變更資料表之所有資料列的查詢更為簡單，所產生的包裝函式也支援下列慣例：

• 如果 @start\_time 參數為 Null，包裝函式會使用擷取執行個體中最低的 LSN 值，做為查詢的下限。

• 如果 @end\_time 參數為 Null，包裝函式會使用擷取執行個體中最高的 LSN 值，做為查詢的上限。

大部分使用者應該都可以在不修改的情況下，使用 sys.sp_cdc_generate_wrapper_function 系統預存程序所建立的包裝函式。不過，若要自訂包裝函式，您必須先自訂 CREATE 指令碼，然後再執行這些指令碼。

當您的封裝呼叫包裝函式時，該封裝必須提供三個參數的值。這三個參數就像異動資料擷取函數所使用的三個參數。這三個參數如下所示：

• 間隔的開始日期/時間值和結束日期/時間值。當包裝函式使用日期/時間值做為查詢間隔的端點時，異動資料擷取函數會使用兩個 LSN 值做為端點。

• 資料列篩選器。對於包裝函式與異動資料擷取函數而言，@row_filter_option 參數相同。如需詳細資訊，請參閱＜cdc.fn_cdc_get_all_changes_<capture_instance> (Transact-SQL)＞和＜cdc.fn_cdc_get_net_changes_<capture_instance> (Transact-SQL)＞。

包裝函式所傳回的結果集包含下列資料：

• 異動資料的所有要求資料行。

• 名稱為 __CDC_OPERATION 的資料行使用一或兩個字元欄位來識別與資料列關聯的作業。此欄位的有效值如下：‘I’ 用於插入、‘D’ 用於刪除、‘UO’ 用於更新舊值，而 ‘UN’ 用於更新新值。

• 當您要求時，更新顯示為作業碼後之位元資料行的旗標，並以 @update\_flag\_list 參數中指定的順序顯示。這些資料行的命名方式是將 ‘_uflag’ 附加到關聯的資料行名稱。

如果您的封裝呼叫查詢所有變更的包裝函式，該包裝函式也會傳回 __CDC_STARTLSN 和 __CDC_SEQVAL 資料行。這兩個資料行會分別成為結果集的第一和第二個資料行。此包裝函式也會根據這兩個資料行，排序結果集。

撰寫您自己的資料表值函數

您也可以改用 SQL Server Management Studio 撰寫呼叫異動資料擷取查詢函數的資料表值包裝函式，並將資料表值包裝函式儲存在 SQL Server 中。如需有關如何建立 Transact-SQL 函數的詳細資訊，請參閱＜CREATE FUNCTION (Transact-SQL)＞。

下列範例定義的資料表值函數可從 Customer 資料表中擷取指定之變更間隔的變更。此函數會使用異動資料擷取函數，將 datetime 值對應到變更資料表在內部使用的二進位記錄序號 (LSN) 值。此函數也會處理數個特殊狀況：

• 針對開始時間傳遞 null 值時，此函數會使用最早可用的值。

• 針對結束時間傳遞 null 值時，此函數會使用最晚可用的值。

• 當開始 LSN 等於結束 LSN (通常表示沒有所選間隔的記錄) 時，此函數會結束。

查詢變更資料之資料表值函數的範例

CREATE function CDCSample.uf_Customer (
     @start_time datetime
    ,@end_time datetime
)
returns @Customer table (
     CustomerID int
    ,TerritoryID int
    ,CustomerType nchar(1)
    ,rowguid uniqueidentifier
    ,ModifiedDate datetime
    ,CDC_OPERATION varchar(1)
) as
begin
    declare @from_lsn binary(10), @to_lsn binary(10)

    if (@start_time is null)
        select @from_lsn = sys.fn_cdc_get_min_lsn('Customer')
    else
        select @from_lsn = sys.fn_cdc_increment_lsn(sys.fn_cdc_map_time_to_lsn('largest less than or equal',@start_time))

    if (@end_time is null)
        select @to_lsn = sys.fn_cdc_get_max_lsn()
    else
        select @to_lsn = sys.fn_cdc_map_time_to_lsn('largest less than or equal',@end_time)

    if (@from_lsn = sys.fn_cdc_increment_lsn(@to_lsn))
        return

    -- Query for change data
    insert into @Customer
    select 
        CustomerID,    
        TerritoryID, 
        CustomerType, 
        rowguid, 
        ModifiedDate, 
        case __$operation
                when 1 then 'D'
                when 2 then 'I'
                when 4 then 'U'
                else null
         end as CDC_OPERATION
    from 
        cdc.fn_cdc_get_net_changes_Customer(@from_lsn, @to_lsn, 'all')

    return
end 
go

擷取包含異動資料的其他中繼資料

雖然之前顯示之使用者建立的資料表值函數僅使用 __$operation 資料行，但是 cdc.fn_cdc_get_net_changes_<capture_instance> 函數會針對每個變更資料列傳回四個中繼資料的資料行。如果您要在資料流程中使用這些值，您可以傳回它們，當做資料表值包裝函式的其他資料行。

資料行名稱	資料類型	說明
__$start_lsn	binary(10)	與變更之認可交易相關聯的 LSN。 在相同交易中認可的所有變更都會共用相同的認可 LSN。例如，如果來源資料表上的更新作業修改了兩個不同的資料列，此變更資料表將會包含四個資料列 (其中兩個是舊值，而另外兩個是新值)，而且每個資料列都包含相同的 __$start_lsn 值。
__$seqval	binary(10)	用來排序交易內資料列變更的序列值。
__$operation	int	與變更相關聯的資料操作語言 (DML) 作業。可以是下列其中一項： 1 = 刪除 2 = 插入 3 = 更新 (更新作業之前的值)。 4 = 更新 (更新作業之後的值)。
__$update_mask	varbinary(128)	位元遮罩，可根據變更資料表的資料行序數識別這些變更的資料行。如果您必須判斷已經變更的資料行，可以檢查這個值。
<擷取的來源資料表資料行>	非固定	這個函數所傳回的其餘資料行都是建立擷取執行個體時，在來源資料表中識別成擷取資料行的資料行。如果擷取的資料行清單中沒有以序數指定任何資料行，就會傳回來源資料表中的所有資料行。

如需詳細資訊，請參閱＜cdc.fn_cdc_get_net_changes_<capture_instance> (Transact-SQL)＞。

下一個步驟

建立查詢變更資料的資料表值函數後，下一個步驟是開始在封裝中設計資料流程。

下一個主題：擷取與了解變更資料

掌握 Integration Services 的最新狀態 若要取得 Microsoft 的最新下載、文件、範例和影片以及社群中的選定解決方案，請瀏覽 MSDN 或 TechNet 上的 Integration Services 頁面： 瀏覽 MSDN 上的 Integration Services 頁面 瀏覽 TechNet 上的 Integration Services 頁面 若要得到這些更新的自動通知，請訂閱該頁面上所提供的 RSS 摘要。