为什么SSIS包创建的全局临时表完成后会消失? [英] Why does a global temp table created by an SSIS package disappear after completion?

问题描述

在 ETL 包开始时，以下 SQL 查询针对 SQL Server 2012 数据库运行:

At the start of ETL package the following SQL query runs against a SQL Server 2012 database:

IF OBJECT_ID(N'tempdb..##SlotChanges') IS NOT NULL
BEGIN
DROP TABLE ##SlotChanges
END

CREATE TABLE ##SlotChanges
(
    --Snip
)

然后包将一些行写入临时表，并在写入目标数据库时将其用作最终数据源.

The package then writes some rows to the temp table and uses it as the final data source when writing to the destination database.

最后没有 drop 命令，因为它只会在下一次运行开始时检查，但是如果我打开 SSMS 并对表运行选择，它会返回一个无效的对象异常.我可以在包运行时通过 SSMS 很好地查询表，但是一旦它完成表就消失了.

There is no drop command at the end, as it will only be checked at the beginning of the next run, but if I then open SSMS and run a select against the table it returns an invalid object exception. I can query the table through SSMS just fine while the package is running, but as soon as it finishes the table's gone.

我的理解是，即使包的会话已经丢失，全局临时表也应该保持不变.我想知道是否有人知道是什么导致包完成后临时表下降/超出范围.

My understanding is that a global temp table should persist even though the package's session has been lost. I'm wondering if anybody knows what's causing the temp table to drop / fall out of scope after the package completes.

推荐答案

当没有任何活动连接使用全局临时表时，它会自动删除.这是一个临时表.

Global temporary table is dropped automatically when there aren't any active connections using it. It's a temporary table.

http://www.sqlservercentral.com/blogs/vivekssqlnotes/2012/01/07/sql-server-global-temporary-tables/

这篇关于为什么SSIS包创建的全局临时表完成后会消失?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！