SSIS 事务

本文摘自：http://www.cnblogs.com/tylerdonet/archive/2011/09/23/2186579.html

在这一个随笔中将介绍在package中如何使用事务来保证数据的完整性和一致性。在SSIS中有两种事务：
 　　分布式事务处理事务（Distributed Transaction Coordinator Transactions）：在task，package中使用单独一个或者多个事务处理
 　　本地事务（Native Transaction）：SQL Server引擎级别的事务，在一个连接中使用T-SQL中的事务

注意：在SQL Server联机丛书中MSDTC是这样定义的：Microsoft 分布式事务处理协调器 (MS DTC) 允许应用程序跨两个或多个 SQL Server 实例扩展事务。此外，该协调器还允许应用程序参与由符合 Open Group DTP XA 标准的事务管理器管理的事务。

在这里我们将用4个小的练习来学习SSIS中的事务，他们分别是：
 　　单个的package：使用DTC建立一个事务
　　 单个的package：使用DTC扩展多个事务
 　　2个package：使用DTC建立一个事务

单个package：使用T-SQL中的事务，就是本地事务
在package中使用该事务需要设置package或者task的属性。如果动手做这些练习，会看到这些属性更加精确的解释，如下图1

图1

　　

下面说明TransactionOption的属性值的具体含义：

Supported ：支持的，如果在父任务中存在事务，该任务也将被添加到事务中来
Not  Supported： 不支持，如果在父任务中发起事务，该任务不会被添加到事务中
Required： 需要的，如果父任务中没有发起事务，该任务将会发起一个事物，如果父任务发起事务，则将自己添加到这个事务中去

　　

　　

单个package，单个事务

创建一个package，命名为SinglePackageSingleTransaction，在Control Flow中拖放3个Execute SQL Task，命名如图2：

 

图2

　　

在数据库中我们可以看到只插入了一条数据，这没有疑问，如果想让这个表T1在下面的task出现错误的情况下不创建该如何设置呢？首先要告诉Package在开始之行的时候开启一个事务，点击空白处按下F4键设置package的TransactionOption属性，如图3:

图3

　　

然后要告诉package中的task，为了防患未然，需要融入到这个事务中来，依次点击选中这些package按下F4键设置它们的TransactionOption属性，如图4

图4

注意：这里需要说明的是，它们默认的选项值就是Supporated，根本不需要我们动手设置，这里啰嗦地说明，只是为了更好的理解它的工作原理。
现在重新执行package，一个DTC事务将会开启，所有的task将被监视，因为最后一个task出现错误，整个package将会被回滚，T1表也不会被创建。可以通过查看Visual Studio的输出（View->Output）来查看信息：

SSIS package "OnePackageOneTracsaction.dtsx" starting.

Information: 0x4001100A at OnePackageOneTracsaction: Starting distributed transaction for this container.

Error: 0xC002F210 at Insert into value ERROR, Execute SQL Task: Executing the query "insert dbo.T1 values('A')" failed with the following error: "Conversion failed when converting the varchar value 'A' to data type int.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

Task failed: Insert into value ERROR

Information: 0x4001100C at Insert into value ERROR: Aborting the current distributed transaction.

Warning: 0x80019002 at OnePackageOneTracsaction: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED.  The Execution method succeeded, but the number of errors raised (1) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.

Information: 0x4001100C at OnePackageOneTracsaction: Aborting the current distributed transaction.

SSIS package "OnePackageOneTracsaction.dtsx" finished: Failure.

　　

单个package，多个事务 

 

 第2个联系我们来在单个package中同时开启两个事务。如图5，创建一个package命名为SinglePackageMultipleTransactions.dtsx。

 

图5

这个package中有两个SequenceContainer，每个都有自己的task。在Start Tran1内开启事务，它的子任务将会添加到事务中。Start Tran2开启另外一个事务，它的子任务会被添加到事务中，但它的子任务故意出错。每个task里面的SQL语句和第1个例子一样。这里我们想开启事务之后，让其可以建立表，插入一条正确的数据。当我们有一些相互分离的子任务，将他们分类放在不同的SequenceContainer中，设置各自的事务，让它们互不干扰，这种方法会很有用。下面列出它们的TransactionOption设置。

任务/容器	TransactionOption设置
Package	Supported
Strat Tran1	Required
Create table	Supported
Insert into value	Supported
Start Tran2	Required
Insert into value ERROR	Supported

设置好这些值之后执行package，结果如图6，第一个Container成功了，第二个失败了。

图6

　　

在数据库中我们看到，表T1被创建，一行数据被插入到表中。

　　

　　

2个package，1个事务

这个练习中我们将新建2个package，分别命名为Caller.dtsx和Called.dtsx。这里我们想使用一个事务包含这2个package。让Caller创建表，然后使用ExecutePackage调用另外一个package，Called，这个package自己也来创建一个表并插入数据。然后故意在Caller这个package中添加一个招致错误的task。我们想让这2个package都回滚，如图6是建好的Caller，图7显示Called。

图6

图7

下面是设置它们的TransactionOption值和SQL语句

Task/Container	TransactionOption value	SQL script
"Caller" Package	Required
CREATE TABLE "Caller"	Supported	if exists(select * from sys.objects where object_id=object_id(N'[dbo].[Caller]') and type in (N'U')) drop table [dbo].[Caller] go create table dbo.Caller(col1 int)
EXECUTE "Called" Package	Supported
Make Things Fail	Supported	insert dbo.Caller values('A')
"Called" Package	Supported
Created Table "Called"	Supported	if exists(select * from sys.objects where object_id=object_id(N'[dbo].[Called]') and type in (N'U')) drop table [dbo].[Called] go create table dbo.Called(col1 int)
Insert Some Rows	Supported	insert into Called values(1)

执行这个Caller，到第3个task的时候遇到错误，两个package都会回滚，看上去像是在执行一个package一样。因为Caller开启一个事务，Called也设置了Supported属性，它也被包含在这个事务中。
如果Caller的TransactionOption设置为NotSupported，它会创建自己的表Caller并插入数据。

单个package，本地Sql Server事务

这个练习和上面的3个都不一样，它不再使用MSDTC来管理事务，而是使用SQL Server中的事务处理。这里演示怎样使用SQL Server自带的事务来从错误中回滚，但是这里的事务只能使用SQL Server数据库，如果连接其他数据库就不能工作了。创建一个package，命名为SinglePackageUsingNativeTracsaction.dtsx，如图8创建拖放3个task。下面给出这3个task的SQLStatement属性

Task	SQLStatement Property Value
BEGIN TRANSACTION	BEGIN TRANSACTION
CREATE TABLE Transactions	CREATE TABLE dbo.Transactions(col1 int)
COMMIT	COMMIT TRANSACTION

要使三个task使用同一个transaction必须保证他们使用同一个数据连接，设置它们所使用的连接的RetainSameConnection为true，如图8

 

 

图8

要使三个task使用同一个transaction必须保证他们使用同一个数据连接，设置它们所使用的连接的RetainSameConnection为true，如图9

图9

从图中我们可以看到，SQL Server新建了事务，最后提交了该事务。