首页 > 代码库 > 在SSIS中的不同组件间使用局部临时表

在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结束时,临时表的生命周期才会终止。

技术分享

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