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新建了事务,最后提交了该事务。
最新文章
- 如何在ASP.NET Core中使用Redis
- 【转】【Raspberry Pi】Unix NetWork Programming:配置unp.h头文件环境
- [DFNews] Blackbag发布MacQuisition 2013 R2
- SDUT图结构练习——最小生成树
- php校验
- Android(java)学习笔记221:开发一个多界面的应用程序之不同界面间互相传递数据(短信助手案例)
- Html.RenderPartial和Html.RenderAction的区别
- SSH连接不上
- Ng-model undefined in the controller
- 6步就能搞出个react网站哈,玩一把!
- linux下内存的统计和内存泄露类问题的定位
- jsp 条件查询、列表分页
- 关于购物车添加按钮的动画(vue.js)
- django--orm关系字段(ForeignKey、OneToOneField、ManyToManyField)详解
- V-rep学习笔记:机器人路径规划2
- Linux下打开超大文件方法
- css选择器,样式表导入
- K8S发布解释型语言应用的最佳实践
- 摘:ClickOnce部署
- j2ee常用jar包
热门文章
- 面试之SQL(1)--选出选课数量&;gt;=2的学号
- Objective-C中单例
- 【转载】C#扫盲之:带你掌握C#的扩展方法、以及探讨扩展方法的本质、注意事项
- liunx安装pip
- kubernetes集群管理命令(二)
- Android组件系列----ContentProvider内容提供者【4】
- tomcat部署web应用的4种方法以及部署多个应用
- Thread join方法的用途
- 从模版生成 uri Golang 的 html/template 包不太适合于这种情况
- [RK3288][Android6.0] 调试笔记 --- 移除uboot和kernel开机logo【转】