Integration Services 事务Integration Services Transactions

适用对象:是SQL Server,包含 Linux 版 是Azure SQL 数据库 是Azure Synapse Analytics (SQL DW) 否并行数据仓库 APPLIES TO: yesSQL Server, including on Linux yesAzure SQL Database yesAzure Synapse Analytics (SQL DW) noParallel Data Warehouse

包使用事务将任务执行的数据库操作绑定到原子单元中,这样做可以维护数据的完整性。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 提供了用于配置事务的三个选项:NotSupported 、Supported 和 Required 。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. 在这种情况下,将由包自身“启动” 此事务,而此包中的各个任务和容器参与此单个事务。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 属性设置为 必需可以将包配置为启动此事务。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 属性设置为 支持To 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 属性设置为“必需” 。In 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 属性设置为“支持” 。In 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 属性设置为 支持可以将包配置为支持多个事务。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 属性设置为 必需To 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 属性设置为“支持” 。In 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 属性设置为“必需” 。In 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 属性设置为“支持” 。In 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 任务都将启动自己的事务,如果序列任务上的事务被中止,则执行 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:

  • 该包由执行包任务调用。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 设置为 RequiredTransactionOption 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