Why does a global temp table created by an SSIS pa

Question

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.

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.

Answer 1

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/

Answer 2

your understaing is bit incorrect. Global Temp table get drop when last session using it compelets.

" Global temporary tables are automatically dropped when the session that created the table ends and all other tasks have stopped referencing them. The association between a task and a table is maintained only for the life of a single Transact-SQL statement. This means that a global temporary table is dropped at the completion of the last Transact-SQL statement that was actively referencing the table when the creating session ended. "

chekc the MSDN Create Table