在SSIS中的不同组件间使用局部临时表

Connetion的属性RetainSameConnection是个boolean值，指定是否保持相同的链接，默认值是false，表示每个component都会单独的使用connection，在component开始时打开connection，在component结束时关闭connection。不同的componet之间使用的是不同的connection。

由于局部临时表的生命周期是在当前连接，在链接关闭时就会终止，临时表就不存在了。在SSIS中不同组件间使用临时表，需要设置Connetion的属性RetainSameConnection=true，这样所有的task，component都会使用同一个连接。临时表的生命周期扩展到不同的componet，task中。

 

示例，在不同的componet，task中使用临时表

1，设计control flow

 

TaskCreate Temporary Table的sql语句

if object_id('tempdb..#dt_temporary') is not null
drop table #dt_temporary

create table #dt_temporary
(
id int
)

Task exec sql statement的sql 语句

insert into #dt_temporary
values(1)

Task Insert into dt_test的sql 语句

insert into dbo.dt_test(id)
select id
from #dt_temporary

Task drop temporary table的sql 语句

drop table #dt_temporary

 

2，设置Connetion的属性RetainSameConnection=true

3，执行package，查看结果

 

4，如果设置Connetion的属性RetainSameConnection=false，在执行package时，会出现错误，

[Execute SQL Task] Error: Executing the query "
insert into #dt_temporary
values(1)" failed with the following error: "Invalid object name '#dt_temporary'.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

 

 

错误原因，上面已经解释清楚：由于Task Create temporary table结束时，其链接关闭，导致创建的临时表生命周期结束，临时表被sql server 销毁。在Task Exec sql statement 中，重新打开链接，不能使用不存在的临时表，所以报错"Invalid object name '#dt_temporary'.".

5，在task中声明的sql 变量，不能在task之间使用，因为sql变量的生命周期是在current batch，而不是connection。每一个task执行的都是一个batch。batch结束，sql变量生命周期就结束。

在 SSMS中，go 语句标识一个batch的结束，下面的语句执行时会报错，就是因为sql 变量的生命周期是在一个batch内。

 

而局部临时表的生命周期是在一个connection中，在当前connection结束时，临时表的生命周期才会终止。