如何使用SSIS程序包中的事务管理

如何使用SSIS程序包中的事务管理
嵌入到SSIS 中的事务处理可以很容易地满足你的要求。

在我们开始进入到在SSIS中执行事务处理的具体步骤之前，让我们先来讨论事务隔离级别，SSIS中高级别的的事务，接着看看在SSIS包中利用事务去解决自己问题的一个实例。

事务隔离级别
事务隔离级别确定保持锁住状态的持续时间。

我们将用SQL Server作为一个例子。

以下事务隔离级别可在SQL Server中利用：
l 未提交读(READ UNCOMMITTED)- 这些读操作没有获得共享锁，它们也不等待锁。

这些一般涉及到脏读，因为你能读没有提交但是已经修改的数据，并且在你读它之后，它可能会回滚。

l 提交读(READ COMMITTED)- 这些读操作获得共享锁，并且等待在事务处理过程中修改的任何数据。

l 重复读(REPEATABLE READ)- 和提交读一样，但此外，在事务处理期间，共享锁保持在行上读。

换句话说，被读的任何行不能被其他的连接操作修改，一直到事务提交或者回滚。

l 序列化(SERIALIZABLE)- 跟重复读一样，但此外，如果在执行SELECT查询之后有新的行出现，那么任何其他的连接操作都不能插入行。

换句话说，如果你通过序列化隔离级别来执行事务中的一个select语句，那么当你在同一个事务处理中再次执行select语句，你会得到完全相同的结果集。

SQL Server 2005增加了两个新的选项：
l 把READ_COMMITTED_SNAPHOT ON设置在数据库级别上的提交读的变化和通过提交读隔离级别的任何事务都不会获得共享锁，也不会等待任何锁。

相反，在SELECT语句开始时，你会得到所有行的提交版本。

l 一个命名为SNAPSHOT的新的隔离级别，在它所在的地方把ALLOW_SNAPSHOT_ISOLATION ON设置在数据库级别上，还有把事务隔离级别设置成snapshot的任何事务都不会获得共享锁，也不会等待任何锁。

相反，在SELECT语句开始时，你会得到所有行的提交版本。

上述两个SQL Server 2005改进是由在tempdb(参考为version store)中保持行的提交版本而促成的。

当一次读操作遇到已经被修改但没有提交的一行，它从version store中重新找回适当的最新提交的行。

SQL Server自动执行version store的维护和通过。

代码不需要做改动。

SSIS中的事务管理
事务支持内置到SSIS中。

TransactionOption属性存在于程序包级别、容器级别(比如For Loop、Foreach Loop和Sequence容器)，还有只与任何控制流任务(比如，执行SQL任务，数据流任务等)相关的级别。

l Required –如果一个事务已经启动，那么容器会加入其中，否则，容器本身会启动一个新的事务。

l Supported –如果已经存在一个事务，那么容器会加入其中，它本身不会启动一个事务(这是默认情况)。

l NotSupported –不会加入现有事务中。

SSIS中的内置事务支持利用Microsoft 分布式事务处理协调器(MSDTC)服务，这个服务必须正在运行。

MSDTC也允许你执行分布式事务，比如在相同的事务中，更新一个SQL Server数据库和一个Oracle数据库。

如果你执行一个使用了内置事务支持的SSIS程序包而MSDTC没有正在运行，那么你会得到如下的错误信息：
Error: 0xC001401A at Transaction: The SSIS Runtime has failed
to start the distributed transaction due to error 0x8004D01B
"The Transaction Manager is not available.". The DTC transaction
failed to start. This could occur because the MSDTC Service is not running.
还要注意的是，在默认的序列化隔离级别中，SSIS 程序包内容也包含一个IsolationLevel属性。

正如上文关于事务隔离级别的讨论，设置会影响锁的持续时间还有共享锁是否能够获得。

SSIS程序包示例
让我们来看看一个示例SSIS程序包，这个程序包将用于展示如何在程序包级别上执行事务和在程序包执行期间锁住一张表。

Test Initialization sequence容器用于创建一个测试环境。

有两张表(TranQueue和TranQueueHistory)被创建，还有一行被插入到TranQueue表中。

这可以帮助我们模拟SSIS程序包在一个事务内处理一组行的过程。

Test Initialization sequence容器中的TransactionOption设置是NotSupported，因为这个设置只用于创建测试环境。

例如，在这里，我们不需要任何回滚事件失败中的任何成功步骤的事务支持。

Process sequence容器有它自身的TransactionOption设置需要支持;由于TransactionOption的程序包设置是Required，所以一个事务创建于程序包级别上，并且这个容器会加入到这个事务中。

Process TranQueue是一个执行SQL任务，这个执行SQL任务会执行如下SQL命令来模拟在TranQueue表中处理一组行的过程：
DELETE TOP(10) dbo.TranQueue
OUTPUT DELETED.*
INTO dbo.TranQueueHistory
FROM dbo.TranQueue WITH (TABLOCKX)
这个SQL命令的要点是：
l 它从TranQueue表中删除十行，以此来模拟抽取它们来处理的过程。

l 它使用OUTPUT从句来把删除的每一行的信息列插入到TranQueueHistory表中，以此来模拟处理过程已经完成，并且历史数据正在被修改。

l 它使用TABLOCKX表线索来锁住TranQueue表。

Placeholder for Breakpoint执行SQL任务没有执行一个命令;它只是在那里，所以当程序包正在运行且事务是打开的(下面将进行讨论)时，我们可以设置一个断点并且执行一些查询。

如果程序包变量v_SimulateFailure = 1，那么Placeholder for Breakpoint执行SQL任务将被执行。

它执行一个SELECT 1/0来产生一个错误(比如用零来除)，这个错误会导致程序包事务的回滚。

上述例子只是为了展示的目的。

当然，你可以在Process sequence容器上执行多种任务，所有这些任务都会参与到事务中，并且要么全部成功，要么没有一个成功(比如，回滚或者失败)。

你可以下载包含示例SSIS程序包的项目。

这个包是固定的，这样可以使用一个名为mssqltips的本地数据库。

如果它不存在，那么创建一个。

通过SQL Server Business Intelligence Development Studio 打开项目并且双击程序包Transaction.dtsx。

按照以下步骤来看SSIS程序包中的事务处理：
l 确定变量的值v_SimulateFailure = 1;这个会显示回滚。

l 确定在Placeholder for Breakpoint执行SQL任务上有一个断点。

l 执行这个程序包;你的屏幕将显示如下(在断点处停止)：
l 在SQL Server Management Studio中打开一个新的查询窗口，连接到mssqltips数据库并且执行如下的命令。

你应该看到一个单行结果集;比如，Test Message2008-09-08 14:22:31.043(当然，你的日期和时间会不同)。

NOLOCK线索忽略了锁;你看到的行也还没有提交。

SELECT * FROM dbo.TranQueueHistory WITH (NOLOCK)
l 在SQL Server Management Studio中打开另一个新的查询窗口，连接到mssqltips数据库并且执行下面的命令。

你会被锁住，等待在SSIS程序包中执行的事务，这样的结果要么是回滚，要么是提交，这是因为我们增加了TABLOCKX线索，这个线索会在事务处理期间让TranQueue表保持锁住状态。

另一种选择是，执行INSERT INTO the dbo.TranQueue table，接着你会看到它也被锁住了，直到这个事务回滚或者提交。

SELECT * FROM dbo.TranQueue
l 点击Continue in BIDS(或者点击最高级别的菜单中的Debug然后继续)，你会看到程序包失败。

在TranQueueHistory表中再次执行上面的SELECT语句，你会看到没有行出现。

上面TranQueue表中的select 语句只完整地显示一行。

因此，这个错误会导致事务回滚。

回滚之后，TranQueue表中的删除的行将被恢复，TranQueueHistory表中的插入的行没有被提交(也就是说，它们将会消失)。

你可以把变量v_SimulateFailure的值设为0，然后再次运行上面的程序包和查询，以此来确认事务提交正如我们所期待的那样进行。