Global Temp tables created in Sequence Container don't persist

Question

I'm moving data from a server that I have limited permissions on so I'm stuck with ##Temp tables or one giant script to process data before writing it to our main server. Processing the data requires 10 different queries. I can run five of them at the same time so I put them in a Sequence Container. All five of them create ##Temps which are later used in the main query. However...none of them are visible after they complete. If I take them all out of the container and run them in sequence one at a time...they all persist. So it's the Sequence Container somehow causing the global temp tables to delete immediately after I'm done filling them.

I 100% have RetainSameConnection = True in my ConnectionManager.

ChatG suggested maybe setting IsolationLevl = ReadUncommitted...did not help.

I don't have to put them in a sequence container but it would be more efficient if I could. Both SQL and Visual Studio are 2019. Any ideas?

Answer 1

RetainSameConnection won't help if you have multiple operations that need to access the Connection Manager at the same time.

You could create the tables in an initial script task and save the open connection to a package variable. This should keep those global temp tables alive for all the connections opened by the Connection Manger.

So

• create a package variable called "SqlConnection"
• create a Script Task in your control flow with read/write access to that variable
• Open a SqlConnection and create the global temp tables in the script task, eg:

        /// </summary>
        public void Main()
        {

            var con = new SqlConnection("Server=localhost;Database=master;Integrated Security=true");
            con.Open();
            var cmd = con.CreateCommand();
            cmd.CommandText = "Create table ##foo(id int, msg varchar(2000))";
            cmd.ExecuteNonQuery();

            Dts.Variables["User::SqlConnection"].Value = con;

            Dts.TaskResult = (int)ScriptResults.Success;
        }

• Add another script task to close the connection
• Put your sequence container between the two script tasks, and all the tasks in the sequence container will have access to the global temp tables.