Not able to use temp tables from SSIS

Question

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. Delay Validation and Retain Same Connection.

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

I have explained that above

Answer 1

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

Create an OLEDB Connection manager with RetainSameConnection set to True

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

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)