在SSIS的组件中,很多都会连接到数据库进行操作,Connection有一个属性RetainSameConnection,默认值是False,控制着连接的打开和关闭的时机。
1,如果Connection的属性RetainSameConnection值是False,那么SSIS的每个组件在开始使用连接时,打开连接,在组件结束时,关闭连接;在连接结束时,如果存在未提交的事务,那么组件会自动回滚该连接内的sql 语句。所以每个组件都会打开和关闭连接,每个组件使用的连接都是不同的。
有两个组件,使用的是同一个连接管理器,在一个组件中创建一个临时表或临时变量,在另一个组件中是不能使用的,原因是在第一个组件结束时,连接也被关闭,临时表或临时变量的生命周期结束,因此不能在另一个连接中使用。
2,如果Connection的属性RetainSameConnection值是true,那么connection会保持打开,直到package结束,connection才会关闭。在connection关闭时,会检查连接中事务存在未提交的事务,如果存在,进行事务回滚。
有两个组件,使用的是同一个连接管理器,在一个组件中创建一个临时表或临时变量,在另一个组件中是可以使用的,原因是在第一个组件结束时,连接没有被关闭,两个组件使用的是同一个连接,临时表或临时变量的生命周期会持续到package结束,所以能在另一个组件中使用。
3,组件失败,会自动进行事务的回滚
如果使用begin tran开启一个explicit事务,当组件失败时,ssis回滚该事务。
示例,利用sql 的begin/commit/rollback tran来实现事务的提交或回滚
查看Connection的属性RetainSameConnection,默认值是False,即每一个task都会单独地使用这个连接,每个task都会单独的打开和关闭这个connection。将这个属性修改为true,所有的task都会使用同一个连接。
在循环任务中需要连接数据库时这个属性设置很重要,它将避免多次打开,关闭连接。将Connetion的属性RetainSameConnection设置为true,只在package开始执行时,打开connection,package结束时,关闭connection,保证所有task使用的都是同一个connection。
package的control flow design如下图
创建示例表
create table dbo.dt_test (id int)
exec sql statment 这个task的sql语句是
insert into dbo.dt_test values(1) insert into dbo.dt_test values('a')
RetainSameConnection保持默认值false,执行时会报错
错误原因可以从Progess选项卡中查看
Task exec sql statment的错误原因,很明显,插入数据的数据类型不对
[Execute SQL Task] Error: Executing the query "insert into dbo.dt_test values('a')
insert into d..." 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 rollback的错误原因是:没有begin tran 子句,由于RetainSameConnection为false,每个task都是单独打开和关闭connection,所以在Task rollback中并没有begin tran 子句。
[Execute SQL Task] Error: Executing the query "rollback tran" failed with the following error: "The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
将RetainSameConnection设置为true,使所有的task的connection都是相同的。再次执行,还是失败,错误原因是:
[Execute SQL Task] Error: Executing the query "rollback tran" failed with the following error: "The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
大胆猜测是由于Execute SQL Task 在失败时,自动进行事务回滚。修改Package,增加一个Task Insert Statement,执行的sql 语句是:
insert into dbo.dt_test values(0)
并在Exec sql statement task上增加breakpoint,在执行task之前触发断点。
重新执行package,ssis停在断点处,查看表dt_test,0被插入到表中
select必须加上with(nolock),否则会被阻塞,表明事务还没有被提交。
继续执行,查看表dt_test,数据已经被回滚。
结论1:当Execute Sql task执行失败时,SSIS自动进行事务的回滚,但是当Execute Sql task执行成功时,不会自动进行explicit事务的提交,开启一个explicit事务,需要显式提交事务。
修改exec sql statment,将执行的sql修改为正确的sql语句,那么将进行commit tran task将进行事务的提交。
insert into dbo.dt_test values(1)
将commit tran task disable,package执行完成之后,package检测到有未提交的事务,package会将这些未提交的事务回滚。
结论2:当连接关闭束时,SSIS会将未提交的explicit事务进行回滚。ssis会检查每一个连接内是否存在未提交的explicit事务,如果存在,那么回滚该事务;对于implicit事务,当sql语句执行结束时,会自动commit。
将Connetion的属性RetainSameConnection设置为false,事务的scope是不同的,其回滚的scope是不同的。
修改begin tran task执行的sql语句,package执行将begin tran task的语句进行回滚,而不会回滚insert statement 和exec statement。
begin tran insert into dbo.dt_test values(3)
缺点,这种事务处理的原理是:保持连接的一致性,利用sql server的事务进行ssis组件的事务处理,但是sql server的事务处理是不能跨数据库的。
使用Connetion的属性RetainSameConnection进行事务处理有个明显的缺点,就是只能在同一个connection下,ssis经常需要处理多个数据库的数据,因此对于跨数据库的事务处理,这种方式是无能为力的,ssis自带的MSDTC(微软分布式事务服务)能够处理跨数据库事务。