定義狀態變數Define a State Variable

此程序描述如何定義 CDC 狀態儲存所在的封裝變數。This procedure describes how to define a package variable where the CDC state is stored.

CDC 狀態變數是由 CDC 控制工作所載入、初始化及更新,並且由 CDC 來源資料流程元件用來判斷變更記錄目前的處理範圍。The CDC state variable is loaded, initialized, and updated by the CDC Control task and is used by the CDC Source data flow component to determine the current processing range for change records. CDC 狀態變數可定義於 CDC 控制工作和 CDC 來源通用的任何容器上。The CDC state variable can be defined on any container common to the CDC Control task and the CDC source. 這可以是在封裝層級,但也可以是在其他容器,如迴圈容器。This may be at the package level but may also be on other containers such as a loop container.

不建議手動修改 CDC 狀態變數值,但這有助於您了解其內容。Manually modifying the CDC state variable value is not recommended, however it can be useful to understand its contents.

下表提供 CDC 狀態變數值各項元件的進階說明。The following table provides a high-level description of the components of the CDC state variable value.

元件Component 描述Description
<狀態名稱><state-name> 此為目前 CDC 狀態的名稱。This is the name of the current CDC state.
CSCS 此標示目前處理範圍的起點 (Current Start)。This marks the current processing range start point (Current Start).
<cs-lsn><cs-lsn> 此為上一個 CDC 回合最後處理的 LSN (記錄序號)。This is the last (Log Sequence Number) LSN processed in the previous CDC run.
CECE 此標示目前處理範圍的終點 (Current End)。This marks the current processing range end point (Current End). CDC 狀態中若存在 CE 元件,代表目前正在處理 CDC 封裝,或是 CDC 封裝在其 CDC 處理範圍未處理完全之前即已失敗。The presence of the CE component in the CDC state is an indication that either a CDC package is currently processing or that a CDC package failed before fully processing its CDC processing range.
<ce-lsn><ce-lsn> 此為目前 CDC 回合要處理的最後一個 LSN。This is the last LSN to be processed in the current CDC Run. 處理的最後一個序號一律假設為最大值 (0xFFF…)。It is always assumed that the last sequence number to be processed is the maximum (0xFFF…).
IRIR 此標示初始處理範圍。This marks the initial processing range.
<ir-start><ir-start> 此為初始載入剛要開始前之異動的 LSN。This is an LSN of a change just before the initial load began.
<ir-end><ir-end> 此為初始載入才剛結束後之異動的 LSN。This is an LSN of a change just after the initial load ended.
TSTS 此標示上次 CDC 狀態更新的時間戳記。This marks the timestamp for the last CDC state update.
<時間戳記><timestamp> 此為 64 位元 System.DateTime.UtcNow 屬性的十進位表示法。This is a decimal representation of the 64-bit, System.DateTime.UtcNow property.
ERER 此將在上次作業失敗時出現,且包含錯誤原因的簡短描述。This appears when the last operation failed and includes a short description of the cause of the error. 若存在此元件,則其一定出現於最後。If this component is present, it will always appear last.
<簡短的錯誤描述><short-error-text> 此為簡短的錯誤描述。This is the short error description.

每個 LSN 和序號都是編碼為多達 20 位數的十六進位字串,代表 Binary(10) 的 LSN 值。The LSNs and sequence numbers are each encoded as a hexadecimal string of up to 20 digits representing the LSN value of Binary(10).

下表描述可能的 CDC 狀態值。The following table describes the possible CDC state values.

StateState 描述Description
(INITIAL)(INITIAL) 這是目前的 CDC 群組上有任何封裝執行之前的初始狀態。This is the initial state before the any package was run on the current CDC group. 這也是 CDC 狀態為空白時呈現的狀態。This is also the state when the CDC state is empty.
ILSTART (初始載入開始)ILSTART (Initial Load Started) 這是在 CDC 控制工作的 MarkInitialLoadStart 作業呼叫之後,初始載入封裝開始時的狀態。This is the state when the initial load package starts, after the MarkInitialLoadStart operation call to the CDC Control task.
ILEND (初始載入結束)ILEND (Initial Load Ended) 這是在 CDC 控制工作的 MarkInitialLoadEnd 作業呼叫之後,初始載入封裝順利結束時的狀態。This is the state when the initial load package ends successfully, after the MarkInitialLoadEnd operation call to the CDC Control task.
ILUPDATE (初始載入更新)ILUPDATE (Initial Load Update) 這是緊接於初始載入之後而仍在處理初始處理範圍期間執行 Trickle 摘要更新封裝時的狀態。This is the state on the runs of the trickle feed update package following the initial load, while still processing the initial processing range. 發生於 CDC 控制工作的 GetProcessingRange 作業呼叫之後。This is after the GetProcessingRange operation call to the CDC Control task.

如果使用了 $reprocessing 資料行,其值就會設定為 1,表示封裝可能要重新處理已經位於目標上的資料列。If using the $reprocessing column, it is set to 1 to indicate that the package may be re-processing rows already at the target.
TFEND (Trickle 摘要更新結束)TFEND (Trickle-Feed Update Ended) 這是一般 CDC 回合所預期的狀態。This is the state expected for regular CDC runs. 這種狀態表示上一個回合已順利完成,而且可以啟動具有新處理範圍的新回合。It indicates that the previous run completed successfully and that a new run with a new processing range can be started.
TFSTARTTFSTART 這是 GetProcessingRange 作業呼叫在 CDC 控制工作之後,非初次執行 Trickle 摘要更新封裝時的狀態。This is the state on a non-initial run of the trickle feed update package, after the GetProcessingRange operation call to the CDC Control task.

這種狀態表示一般 CDC 回合已啟動,但是沒有完成或者尚未全部完成 (MarkProcessedRange)。This indicates that a regular CDC run is started but has not finished or has not yet finished, cleanly (MarkProcessedRange).
TFREDO (重新處理 Trickle 摘要更新)TFREDO (Reprocessing Trickle-Feed Updates) 這是在 TFSTART 之後發生 GetProcessingRange 時的狀態。This is the state on a GetProcessingRange that occurs after TFSTART. 這種狀態表示上一個回合並未順利完成。This indicates that the previous run did not complete successfully.

如果使用了 $reprocessing 資料行,其值就會設定為 1,表示封裝可能要重新處理已經位於目標上的資料列。If using the $reprocessing column, it is set to 1 to indicate that the package may be re-processing rows already at the target.
ERRORERROR CDC 群組處於 ERROR 狀態。The CDC group is in an ERROR state.

以下是 CDC 狀態變數值的範例。The following are examples of CDC state variable values.

  • ILSTART/IR/0x0000162B158700000000//TS/2011-08-07T17:10:43.0031645/ILSTART/IR/0x0000162B158700000000//TS/2011-08-07T17:10:43.0031645/

  • ILSTART/IR/0x0000162B158700000000//TS/2011-08-07T17:10:43.0031645/ILSTART/IR/0x0000162B158700000000//TS/2011-08-07T17:10:43.0031645/

  • TFEND/CS/0x0000025B000001BC0003/TS/2011-07-17T12:05:58.1001145/TFEND/CS/0x0000025B000001BC0003/TS/2011-07-17T12:05:58.1001145/

  • TFSTART/CS/0x0000030D000000AE0003/CE/0x0000159D1E0F01000000/TS/2011-08-09T05:30:43.9344900/TFSTART/CS/0x0000030D000000AE0003/CE/0x0000159D1E0F01000000/TS/2011-08-09T05:30:43.9344900/

  • TFREDO/CS/0x0000030D000000AE0003/CE/0x0000159D1E0F01000000/TS/2011-08-09T05:30:59.5544900/TFREDO/CS/0x0000030D000000AE0003/CE/0x0000159D1E0F01000000/TS/2011-08-09T05:30:59.5544900/

若要定義 CDC 狀態變數To define a CDC state variable

  1. SQL Server Data ToolsSQL Server Data Tools中,開啟有需要定義變數之 CDC 流程的 SQL Server 2017 Integration Services (SSIS)SQL Server 2017 Integration Services (SSIS) 封裝。In SQL Server Data ToolsSQL Server Data Tools, open the SQL Server 2017 Integration Services (SSIS)SQL Server 2017 Integration Services (SSIS) package that has the CDC flow where you need to define the variable.

  2. 按一下 [封裝總管] 索引標籤,並加入新的變數。Click the Package Explorer tab, and add a new variable.

  3. 為變數指定可辨識的名稱,做為狀態變數。Give the variable a name that you can recognize as your state variable.

  4. 為變數指定 字串 資料類型。Give the variable a String data type.

    不要指定變數值做為其定義的一部分。Do not give the variable a value as part of its definition. 此值必須是由 CDC 控制工作所設定。The value must be set by the CDC Control task.

    如果您打算將 CDC 控制工作與 [自動狀態持續性] 搭配使用,CDC 狀態變數將會從您指定的資料庫狀態資料表讀取,並在其值變更時更新回該相同的資料表。If you plan to use the CDC Control task with Automatic State Persistence, the CDC State variable will be read from the database state table you specify and will be updated back to that same table when its value changes. 如需有關狀態變數的詳細資訊,請參閱< CDC Control Task>和< CDC Control Task Editor>。For more information about the State table, see CDC Control Taskand CDC Control Task Editor.

    如果您不打算將 CDC 控制工作與 [自動狀態持續性] 搭配使用,則必須從上次封裝執行時儲存其值的永續性儲存體中載入變數值,並在目前處理範圍已完成處理時將變數值寫回該永續性儲存體。If you are not using the CDC Control task with Automatic State Persistence then you must load the variable value from persistent storage where its value was saved the last time the package ran and to write it back to the persistent storage when the processing of the current processing range was completed.

另請參閱See Also

CDC Control Task CDC Control Task
CDC 控制工作編輯器CDC Control Task Editor