Integration Services 交易Integration Services Transactions

封裝使用交易將工作執行的資料庫動作繫結至原子單位,這樣可以保持資料的完整性。Packages use transactions to bind the database actions that tasks perform into atomic units, and by doing this maintain data integrity. 所有 MicrosoftMicrosoft Integration ServicesIntegration Services 容器類型 (封裝、For 迴圈、Foreach 迴圈和時序容器,以及封裝每個工作的工作主機) 皆可設定成使用交易。All MicrosoftMicrosoft Integration ServicesIntegration Services container types—packages, the For Loop, Foreach Loop, and Sequence containers, and the task hosts that encapsulate each task—can be configured to use transactions. Integration ServicesIntegration Services 提供三個設定交易的選項,分別是 NotSupportedSupportedRequired provides three options for configuring transactions: NotSupported, Supported, and Required.

  • Required 指出容器會啟動交易,除非其父容器已經將其啟動。Required indicates that the container starts a transaction, unless one is already started by its parent container. 如果交易已經存在,則容器會聯結交易。If a transaction already exists, the container joins the transaction. 例如,如果未設定為支援交易的封裝包括使用 Required 選項的「時序」容器,則「時序」容器會啟動其自己的交易。For example, if a package that is not configured to support transactions includes a Sequence container that uses the Required option, the Sequence container would start its own transaction. 如果封裝設定為使用 Required 選項,則「時序」容器會聯結封裝交易。If the package were configured to use the Required option, the Sequence container would join the package transaction.

  • Supported 指出容器不啟動交易,但會聯結其父容器啟動的任何交易。Supported indicates that the container does not start a transaction, but joins any transaction started by its parent container. 例如,如果具有四個「執行 SQL」工作的封裝啟動交易,且全部四個工作都使用 Supported 選項,則任何工作失敗時,都會回復「執行 SQL」工作執行的資料庫更新。For example, if a package with four Execute SQL tasks starts a transaction and all four tasks use the Supported option, the database updates performed by the Execute SQL tasks are rolled back if any task fails. 如果封裝未啟動交易,則四個「執行 SQL」工作不會由交易繫結,且只會回復由失敗之工作執行的資料庫更新。If the package does not start a transaction, the four Execute SQL tasks are not bound by a transaction, and no database updates except the ones performed by the failed task are rolled back.

  • NotSupported 指出容器不啟動交易或聯結現有的交易。NotSupported indicates that the container does not start a transaction or join an existing transaction. 父容器啟動的交易不會影響已設定為不支援交易的子容器。A transaction started by a parent container does not affect child containers that have been configured to not support transactions. 例如,如果封裝設定為啟動交易,且封裝中的「For 迴圈」容器使用 NotSupported 選項,則「For 迴圈」中的工作一旦失敗將無法回復。For example, if a package is configured to start a transaction and a For Loop container in the package uses the NotSupported option, none of the tasks in the For Loop can roll back if they fail.

    您可以設定容器上的 TransactionOption 屬性來設定交易。You configure transactions by setting the TransactionOption property on the container. 使用 中的 [屬性] SQL Server Data Tools (SSDT)SQL Server Data Tools (SSDT)視窗,可以設定此屬性,也可以程式設計方式設定屬性。You can set this property by using the Properties window in SQL Server Data Tools (SSDT)SQL Server Data Tools (SSDT), or you can set the property programmatically.

注意

TransactionOption 屬性會影響是否會套用容器所要求的 IsolationLevel 屬性值。The TransactionOption property influences whether or not the value of the IsolationLevel property requested by a container is applied. 如需詳細資訊,請參閱< 設定封裝屬性 >主題中的 IsolationLevel屬性描述。For more information, see the description of the IsolationLevel property in the topic, Setting Package Properties.

設定封裝來使用交易Configure a package to use transactions

將封裝設定成使用交易時,您有兩個選項:When you configure a package to use transactions, you have two options:

  • 擁有封裝的單一交易。Have a single transaction for the package. 在此情況下,封裝本身會 「起始」 (Initiate) 這筆交易,而封裝中的個別工作和容器會參與這個單一交易。In this case, it is the package itself that initiates this transaction, whereas individual tasks and containers in the package participate in this single transaction.

  • 在封裝中擁有多個交易。Have multiple transactions in the package. 在此情況下,雖然封裝支援交易,但是封裝中的個別工作和容器實際上會起始交易。In this case, the package supports transactions, but individual tasks and containers in the package actually initiate the transactions.

    下列程序將描述如何設定這兩個選項。The following procedures describe how to configure both options.

設定封裝以使用單一交易Configure a package to use a single transaction

在這個選項中,封裝本身會起始單一交易。In this option, the package itself initiates a single transaction. 您可以將封裝的 TransactionOption 屬性設定為 Required,藉以將此封裝設定成起始這筆交易。You configure the package to initiate this transaction by setting the TransactionOption property of the package to Required.

接著,您可以在這個單一交易中編列特定工作和容器。Next, you enlist specific tasks and containers in this single transaction. 若要在交易中編列工作或容器,您可以將該工作或容器的 TransactionOption 屬性設定為 SupportedTo enlist a task or container in a transaction, you set the TransactionOption property of that task or container to Supported.

  1. SQL Server Data Tools (SSDT)SQL Server Data Tools (SSDT)中,開啟包含您要設定以使用交易之封裝的 Integration ServicesIntegration Services 專案。In SQL Server Data Tools (SSDT)SQL Server Data Tools (SSDT), open the Integration ServicesIntegration Services project that contains the package you want to configure to use a transaction.

  2. 在 [方案總管] 中,按兩下封裝將其開啟。In Solution Explorer, double-click the package to open it.

  3. 按一下 [控制流程] 索引標籤。Click the Control Flow tab.

  4. 以滑鼠右鍵按一下控制流程設計介面背景的任何位置,然後按一下 [屬性]。Right-click anywhere in the background of the control flow design surface, and then click Properties.

  5. 在 [屬性] 視窗中,將 TransactionOption 屬性設定為 RequiredIn the Properties window, set the TransactionOption property to Required.

  6. 在 [控制流程] 索引標籤的設計介面上,以滑鼠右鍵按一下您要在交易中註冊的工作或容器,然後按一下 [屬性]。On the design surface of the ControlFlow tab, right-click the task or the container that you want to enroll in the transaction, and then click Properties.

  7. 在 [屬性] 視窗中,將 TransactionOption 屬性設定為 SupportedIn the Properties window, set the TransactionOption property to Supported.

    注意

    若要在交易中編列連接,請註冊在交易中使用連接的工作。To enlist a connection in a transaction, enroll the tasks that use the connection in the transaction. 如需詳細資訊,請參閱 Integration Services (SSIS) 連接For more information, see Integration Services (SSIS) Connections.

  8. 針對您想要在此交易中註冊的每個工作和容器,重複步驟 6 和 7。Repeat steps 6 and 7 for each task and container that you want to enroll in the transaction.

設定封裝以使用多個交易Configure a package to use multiple transactions

在這個選項中,雖然封裝本身支援交易,但是不會啟動交易。In this option, the package itself supports transactions but does not start a transaction. 您可以將封裝的 TransactionOption 屬性設定為 Supported,藉以將此封裝設定成支援交易。You configure the package to support transactions by setting the TransactionOption property of the package to Supported.

接著,您可以將封裝內部的所需工作和容器設定成起始或參與交易。Next, you configure the desired tasks and containers inside the package to initiate or participate in transactions. 若要將工作或容器設定成起始交易,您可以將該工作或容器的 TransactionOption 屬性設定為 RequiredTo configure a task or container to initiate a transaction, you set the TransactionOption property of that task or container to Required.

  1. SQL Server Data Tools (SSDT)SQL Server Data Tools (SSDT)中,開啟包含您要設定成使用交易之封裝的 Integration ServicesIntegration Services 專案。In SQL Server Data Tools (SSDT)SQL Server Data Tools (SSDT), open the Integration ServicesIntegration Services project that contains the package you want to configure to use transaction.s

  2. 在 [方案總管] 中,按兩下封裝將其開啟。In Solution Explorer, double-click the package to open it.

  3. 按一下 [控制流程] 索引標籤。Click the Control Flow tab.

  4. 以滑鼠右鍵按一下控制流程設計介面背景的任何位置,然後按一下 [屬性]。Right-click anywhere in the background of the control flow design surface, and then click Properties.

  5. 在 [屬性] 視窗中,將 TransactionOption 屬性設定為 SupportedIn the Properties window, set the TransactionOption property to Supported.

    注意

    封裝支援交易,但交易是由封裝中的工作或容器所啟動。The package supports transactions, but the transactions are started by task or containers in the package.

  6. 在 [控制流程] 索引標籤的設計介面上,以滑鼠右鍵按一下要啟動其交易之封裝內的工作或容器,然後按一下 [屬性]。On the design surface of the ControlFlow tab, right-click the task or the container in the package for which you want to start a transaction, and then click Properties.

  7. 在 [屬性] 視窗中,將 TransactionOption 屬性設定為 RequiredIn the Properties window, set the TransactionOption property to Required.

  8. 如果交易由容器啟動,請以滑鼠右鍵按一下您要在交易中註冊的工作或容器,然後按一下 [屬性]。If a transaction is started by a container, right-click the task or the container that you want to enroll in the transaction, and then click Properties.

  9. 在 [屬性] 視窗中,將 TransactionOption 屬性設定為 SupportedIn the Properties window, set the TransactionOption property to Supported.

    注意

    若要在交易中編列連接,請註冊在交易中使用連接的工作。To enlist a connection in a transaction, enroll the tasks that use the connection in the transaction. 如需詳細資訊,請參閱 Integration Services (SSIS) 連接For more information, see Integration Services (SSIS) Connections.

  10. 針對啟動交易的每個工作和容器,重複步驟 6 至 9。Repeat steps 6 through 9 for each task and container that starts a transaction.

在封裝中的多個交易Multiple transactions in a package

Integration ServicesIntegration Services 封裝中,封裝可以包含不相關的交易。It is possible for a package to include unrelated transactions in an Integration ServicesIntegration Services package. 任何時候如果巢狀容器階層中間的容器不支援交易,而階層中其上面或下面的容器設定為支援交易,則這些容器就會啟動分別的交易。Any time a container in the middle of a nested container hierarchy does not support transactions, the containers above or below it in the hierarchy start separate transactions if they are configured to support transactions. 交易會從巢狀容器階層中最內層的工作到封裝依序進行認可或回復。The transactions commit or roll back in order from the innermost task in the nested container hierarchy to the package. 不過,內部交易認可後,如果外部交易已中止,則不會回復該交易。However, after the inner transaction commits, it does not roll back if an outer transaction is aborted.

在封裝中的多個交易的範例Example of multiple transactions in a package

例如,封裝具有的「時序」容器包含兩個「Foreach 迴圈」容器,而每個容器包含兩個「執行 SQL」工作。For example, a package has a Sequence container that holds two Foreach Loop containers, and each container include two Execute SQL tasks. 時序容器支援交易,Foreach 迴圈容器不支援,而執行 SQL 工作則支援。The Sequence container supports transactions, the Foreach Loop containers do not, and the Execute SQL tasks do. 在此範例中,每一個執行 SQL 工作都會啟動自己的交易,而即使時序工作上的交易中止,也不會回復。In this example, each Execute SQL task would start its own transaction and would not roll back if the transaction on the Sequence task was aborted.

「時序」容器、「Foreach 迴圈」容器和「執行 SQL」工作的 TransactionOption 屬性設定如下:The TransactionOption properties of the Sequence container, Foreach Loop container and the Execute SQL tasks are set as follows:

  • 「時序」容器的 TransactionOption 屬性會設為 RequiredThe TransactionOption property of the Sequence container is set to Required.

  • 「Foreach 迴圈」容器的 TransactionOption 屬性會設為 NotSupportedThe TransactionOption properties of the Foreach Loop containers are set to NotSupported.

  • 「執行 SQL」工作的 TransactionOption 屬性會設為 RequiredThe TransactionOption properties of the Execute SQL tasks are set to Required.

    下圖顯示封裝中五個不相關的交易。The following diagram shows the five unrelated transactions in the package. 一個交易是由「時序」容器啟動的,四個交易是由「執行 SQL」工作啟動的。One transaction is started by the Sequence container and four transactions are started by the Execute SQL tasks.

    實作多個交易的Implementation of multiple transactions

繼承的交易Inherited transactions

封裝可使用「執行封裝」工作執行另一個封裝。A package can run another package by using the Execute Package task. 子封裝 (亦即「執行封裝」工作所執行的封裝) 可建立其自己的封裝交易,也可以繼承父封裝交易。The child package, which is the package run by the Execute Package task, may create its own package transaction, or it may inherit the parent package transaction.

如果下列兩個情況均成立,則子封裝將會繼承父封裝交易:A child package inherits the parent package transaction if both of the following are true:

  • 由一「執行封裝」工作叫用 (Invoke) 該封裝。The package is invoked by an Execute Package task.

  • 叫用該封裝的「執行封裝」工作亦聯結父封裝交易。The Execute Package task that invoked the package also joined the parent package transaction.

    除非子封裝自行聯結交易,否則子封裝中的容器和工作無法聯結父封裝交易。Containers and tasks in the child package cannot join the parent package transaction unless the child package itself joins the transaction.

繼承的交易的範例Example of inherited transactions

在下圖中,有三個使用交易的封裝。In the following diagram, there are three packages that all use transactions. 每一個封裝都包含多個工作。Each package contains multiple tasks. 為強調交易的行為,只會顯示「執行封裝」工作。To emphasize the behavior of the transactions, only the Execute Package tasks are shown. 封裝 A 執行封裝 B 和 C。而封裝 B 又執行封裝 D 和 E,封裝 C 執行封裝 F。Package A runs packages B and C. In turn, package B runs packages D and E, and package C runs package F.

封裝和工作具有下列交易屬性:Packages and tasks have the following transaction attributes:

  • 在封裝 A 和 C 上,TransactionOption 設為 Required TransactionOption is set to Required on packages A and C

  • 在封裝 B 和 D 上,以及在「執行封裝 B」、「執行封裝 D」和「執行封裝 F」工作上,TransactionOption 設為 SupportedTransactionOption is set to Supported on packages B and D, and on the tasks Execute Package B, Execute Package D, and Execute Package F.

  • 在封裝 E 上,以及在「執行封裝 C」和「執行封裝 E」工作上,TransactionOption 設為 NotSupportedTransactionOption is set to NotSupported on package E, and on the tasks Execute Package C and Execute Package E.

    繼承的交易流程Flow of inherited transactions

    只有封裝 B、D 和 F 可從其父封裝繼承交易。Only packages B, D, and F can inherit transactions from their parent packages.

    封裝 B 和 D 會繼承由封裝 A 啟動的交易。Packages B and D inherit the transaction that was started by package A.

    封裝 F 會繼承由封裝 C 啟動的交易。Package F inherits the transaction that was started by package C.

    封裝 A 和 C 會控制其自己的交易。Packages A and C control their own transactions.

    封裝 E 不使用交易。Package E does not use transactions.

外部資源External Resources

另請參閱See Also

繼承的交易 Inherited Transactions
多個交易Multiple Transactions