Managing One Temporary Table in SSIS

By : Kasim Wirama, MCDBA, MVP SQL Server

In T-SQL, you must have used temporary table to keep temporary resultset. Temporary table is unique per connection, so if there is 2 users creating connection to create temporary table, even though the name is same, but two of them doesn’t interact each other. How it becomes possible? In engine level, SQL Server create temporary table with suffix with connection id.

If you create temporary table in SSIS, you must refers it to the same connection manager, otherwise SSIS will consider the temporary table is created and manipulated under different connection id.

You can create connection manager, pointing to tempdb database. Now I would like to do temporary table creation , insert new record, and drop the table, each of them is put on separate Execute SQL Task.

If you run the package, it will display red background in second task, the error message is : "Invalid object name '’.". It seems that SSIS consider the connection of the second task is different from the connection of the first task, even though both tasks refers to the same connection manager.

You should see property of the connection manager called RetainSameConnection. The default value is false, meaning that SSIS will create another connection session. To just make current connection of one task is shared to other connection session of previous task, you can change the default value from false to true.

Run you package again. This time, SSIS recognises connection session among three packages refers to one connection session.