CODEWITHSUNDEEP
ssistemp-tablesssis-2012control-flowexecute-sql-task
mk SQL
mk SQL

Reputation: 11

Not able to use temp tables in one "Execute SQL Task" after another in SSIS Control Flow

I have two tasks in control flow. The first task is in sequence container and creates multiple global temp tables. The second task uses these temp tables. Both the tasks are in 'Execute SQL Task'. They both use Native OLE DB\SQL Server Native Client 11.0 driver to connect to SQL Server. I have both the properties set to True:

When the first Sequence container runs, I see temp tables getting created through management studio window when they are in 'yellow' or running state. As soon as the first task completes, they do become green(completes), the next task is not able to use these global temp tables. Neither can I see these tables in management studio anymore.

How do I retain these temp tables so that they can be used in same dtsx task in subsequent tasks?

Upvotes: 1

Views: 613

Answers (1)

billinkc
billinkc

Reputation: 61269

As Brad indicates as well as your write up, it seems you're doing everything correct.

Let's try a basic reproduction and let me know whether this does or does not work in your environment. We'll have a two task package, both Execute SQL Tasks

enter image description here

Create an OLEDB Connection manager with RetainSameConnection set to True

enter image description here

SQL Create global temp

The first execute sql task will create the global temporary table

DROP TABLE IF EXISTS ##SO_77411558;
CREATE TABLE ##SO_77411558
(
   Col1 int
);

SQL Use global temp

In our second Execute SQL Task, we'll add a row to the table

INSERT INTO ##SO_77411558
SELECT 3;

At this point, I have greens across the board.

If I add an empty Sequence Container after the second Execute SQL Task and put a breakpoint on it

enter image description here

I can then verify the global tables remain as the package has not completed

sqlcmd -S .\dev2017 -E -d tempdb -Q "SELECT T.name FROM sys.tables AS T WHERE T.name like '%SO%'"
name
-----------------
##SO_77411558

(1 rows affected)

Upvotes: 3

Related Questions