SQL点滴之SSIS中的事务处理
发布时间：2011-04-27 16:09 来源：未知
在介绍了SQL Server中的事务的概念之后，现在我们可以继续介绍这次的点滴了。
我们可以把SSIS中的整个package包含在一个事务中，但是假如在package的执行过程中有一个表需要锁定应该怎么处理呢？SSIS内建的事务处理可以解决这个问题。
SSIS中的包，容器（例如Loop，Foreach Loop，Sequence）或者一个单独的任务中都可以设置事务处理选项。事务处理选项有下面一些值
Required-如有事务则添加，否则新添加一个 Supported-如有有事务添加一个，没有则不添加，这是默认选项+ NotSupported-不添加事务处理内建的事务处理要使用Distributed Transaction Coordinator(MSDTC)服务，这个服务必须开启。MSDTC允许使用分布式事务处理，例如在一个事务中同时处理SQL Server数据库和Oracle数据库。假如没有开启这个服务会得到下面的错误提示.
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中包中的元素的事务隔离级别是Serializable，这种级别会影响锁的持续时间。下面我们来用一个例子说明在如何package中锁定一个表
1.新建一个SequenceContainer，命名为Test Initialization。
2.这个SequenceContainer主要用来创建测试的环境，创建连个表TranQueue，TranQueueHistory，向第一个表中添加一条记录，这样模拟一个事物处理过程。我们只是使用这个SequenceContainer来创建测试环境，所以设置它的TransactionOption选项为NotSupported在这个SequenceContainer中依次添加三个Execute SQL，依次他们的设置如下
/*命名*/ Create TranQueue Table /*SQLstatement设置*/ IF NOT EXISTS ( SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.TranQueue') AND type in (N'U') ) BEGIN execute('CREATE TABLE dbo.TranQueue(message nvarchar(256))') END /*命名*/ Populate TranQueue /*SQLstatement设置*/ INSERT INTO dbo.TranQueue VALUES ('Test Message' + CONVERT (NVARCHAR(23), GETDATE(), 121)) /*命名*/ Create TranQueueHistory table /*SQLstatement设置*/ IF NOT EXISTS ( SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.TranQueueHistory') AND type in (N'U') ) BEGIN execute('CREATE TABLE dbo.TranQueueHistory(message nvarchar(256))') END
3.创建第二个SequenceContainer，命名为Process，TransactionOption属性设置为Supported，这样就会添加事务处理。
4.在这个SequenceContainer中添加一个Execute SQL，命名为ProcessTranQueue，它的SQLStatement设置为下面的语句。这个语句的作用，模拟事务处理，删除TranQueue表中前10条数据；OUTPUT字句将删除的数据插入到TranQueueHistory表中，模拟处理结束，更新历史记录
DELETE TOP(10) dbo.TranQueue OUTPUT DELETED.* INTO dbo.TranQueueHistory FROM dbo.TranQueue WITH (TABLOCKX)
5.添加一个Execute SQL，命名为Placeholder for Breakpoint。这个任务不进行任何操作，只是为了在这设置一个断点然后在这里停下来让我们有时间验证是否会锁定表。
6.右击Control Flow界面添加一个变量v_SimulateFailure，类型为Int32，值为1。 7.添加一个Execute SQL命名为Simulate Failure。用它来模拟错误，设置SQLStatement为select 1/0，当pacakage执行到这里的时候会造成错误进而回滚。 8.右击Placeholder for Breakpoint和Simulate Failure之间的连线，点击Edit，设置Evaluation operation为Expression and Constraint，设置Expression为@[User::v_SimulateFailure] == 1，其他保持默认。这样之后这个自定义变量的值为1的时候才会继续往下执行。
9.执行package，会得到如图1的结果，package在断点处终止。 图1 10.打开SQL Server Management Studion,选择对应的数据库，新建一个Query，执行下面的语句，NOLOCK选项忽略锁，这个语句查询得到一条记录 Message2011-04-10 14:22:31.043，但是这条记录并没有提交 SELECT * FROM dbo.TranQueueHistory WITH (NOLOCK)
11.执行下面的语句 SELECT * FROM dbo.TranQueue
语句将阻塞在这里，语句一直停留在执行状态，不会结束。因为在Process TranQueue任务中我们使用TABLOCKX，在这里将等待任务回滚或者提交。或者可以写成这样，它任然会阻塞 DELETE TOP(10) dbo.TranQueue INSERT INTO dbo.TranQueueHistory VALUES ('Test Message' + CONVERT(NVARCHAR(23), GETDATE(), 121))
12. 点击Continue按钮或者Debuge按钮，会看到package执行失败，执行SELECT * FROM dbo.TranQueueHistory WITH (NOLOCK)；因为执行了回滚，不会得到任何结果。SELECT * FROM dbo.TranQueue，任然有一条记录。 SELECT * FROM dbo.TranQueueHistory WITH (NOLOCK)
NOLOCK提示忽略锁，这个语句查询得到一条记录 Message2011-04-10 14:22:31.043，但是这条记录并没有提交 13. 执行下面的语句， SELECT * FROM dbo.TranQueue
sql语句将阻塞在这里，语句一直执行。因为在Process TranQueue任务中我们使用TABLOCKX，在这里将等待任务回滚或者提交。或者可以写成这样 DELETE TOP(10) dbo.TranQueue; INSERT INTO dbo.TranQueueHistory VALUES ('Test Message' + CONVERT(NVARCHAR(23), GETDATE(), 121))，
它任然会阻塞 14. 点击Continue按钮或者Debuge按钮，会看到package执行失败，执行SELECT * FROM dbo.TranQueueHistory WITH (NOLOCK)；因为执行了回滚，不会得到任何结果。执行SELECT * FROM dbo.TranQueue，任然有一条记录。 假如设置变量User::v_SimulateFailure的值为0，不会执行Simulate Failure任务，就不会回滚，TranQueue中的记录会被写入到TranQueueHistory中。这里有一个很有意思的语句： DELETE TOP(10) dbo.TranQueue OUTPUT DELETED.* INTO dbo.TranQueueHistory FROM dbo.TranQueue WITH (TABLOCKX)
假如两个表的结构有一部分是是一样的，现在想把一个表的数据导入到另外一个表中，可以使用DELETE SourceTable OUTPUT DELETE.*/DELETE.Column1,DELETE.Column2... INTO DestinationTable FROM SourceTable，这样第一个表中的数据会被“剪切”到第二个表中。 原文链接：http://www.cnblogs.com/tylerdonet/archive/2011/04/10/2011806.html
