使用Connetion的属性RetainSameConnection

在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(微软分布式事务服务)能够处理跨数据库事务。