DFT drops connection and its temporary table after leaving any DFT item, tested with two Script Components. How do I keep the temp table alive?

Question

Links

This takes up:

• Using temporary tables in SSIS flow fails - Stack Overflow

• Is it possible to use a temp table in a data flow source?; answer is:

  'No', since it cannot be passed without being dropped right away.

  (Mind that even the working answer below cannot fix this problem of the Data Source item that does not give you a choice whether you want to close or release the connection, it always closes the connection, and with that, the temp table is dropped.)

• Do I lose my temporary tables when changing tasks in an SSIS package? - Stack Overflow

• How does SSIS manage closing connections? Can I force it? - Stack Overflow

• Is it possible to use OleDbConnections with the Script Component?

From this, I thought I should test keeping up the connection between two DFT items so that the temporary table does not get dropped.

Control Flow connection manager does not fix it

With a connection manager for the "tempdb" database, I can make temporary tables in the Control Flow that survive the next step inside the Control Flow, see Use Temp Table in SSIS?. While you might think that How to create a temporary table in SSIS control flow task and then use it in data flow task? already answers this question, I found that the answer there does not fix the problem of dropped temp tables after one step in the DFT.

ADO.NET connection manager does not fix it

I tested the DFT with a ADO.NET connection manager and the temp table was still dropped after the Script Component.

Test setup

I tested it on a Script Component that I put after another:

Setting RetainSameConnection to True does not fix it

I set RetainSameConnection to True as in the other links, but this did not fix it.

Code tricks

I tried it by not making a new connection in the second Script Component but just by acquiring it, to no avail:

• In Script Component 1, both base.PostExecute(); and conn.Close(); are commented out in the hope that this keeps alive the temp table, the code is taken from How do I create and fill a temporary table with incoming data from a Data Source item without leaving the SSIS Data Flow Task C# Script Component?:

    public override void PostExecute()
    {
        //base.PostExecute();
        // here we would bulk copy data into the temp table
        // you may need to batch this operation or handle it differently based on your situation
        using (SqlBulkCopy bulkCopy = new SqlBulkCopy(conn))
        {
            // Now write that DataTable to the database 
            bulkCopy.DestinationTableName = "##tmpTable";
            bulkCopy.WriteToServer(dt);
        }
        //conn.Close();
    }

• In Script Component 2, the same connection is just fetched again with (SqlConnection)Connections.Connection.AcquireConnection(Transaction);:

    public override void AcquireConnections(object Transaction)
    {
        base.AcquireConnections(Transaction);
        conn = (SqlConnection)Connections.Connection.AcquireConnection(Transaction);
    }

The temporary table is dropped already after the end of the first Script Component since the connection seems to be lost by default even if I do not close it.

Quest for a trick that allows me to work with temporary tables in many DFT items and throughout the package/project

I hope to find an answer that keeps alive the temporary table with some sort of trick. It must all work only from SSIS, I cannot just make the temporary table in SSMS only to keep it alive for SSIS.

If the DFT drops temporary tables after leaving any DFT item, how can I keep the temporary table alive within the working connection manager, or what is the workaround for it?

Further work

I made a full scale check of the given answer and found out that I could not build a temporary table in one Script Component and read it in the next without losing the chance of passing it to a destination table at the very end.

I made this a new question at How do I feed the output arrow (Input0_ProcessInputRow()) of a second Script Component with data from a temp table of the first Script Component?

I guess that this question was not asked yet since developers put all of the code in just one Script Component. If you do the calculations in the PreExecute() method, you can pass the data from the temporary tables to the output arrow to fill a regular destination table.

I try to split the C# script into many C# scripts so that the data flow becomes as clear as it would be without C#. But this is not the aim of anyone else I guess. Perhaps they do not need to split up their large C# code, it makes it too complicated then. They just code everything in one go, then you do not need to ask the question.

Answer 1

All you have to do is add an ADO.NET connection manager for SQL Server to your project and set it to "RetainSameConnection". Then each script task can access the same SqlConection object. eg

        var con = (SqlConnection) this.Connections.Connection.AcquireConnection(null); // Acquire the connection

        var cmd = con.CreateCommand();
        cmd.CommandText = "select 'helo temp table' msg into #foo";
        var msg = cmd.ExecuteNonQuery();
        
        this.Connections.Connection.ReleaseConnection(con); // Release the connection

then a subsequent script can run

        var con = (SqlConnection)this.Connections.Connection.AcquireConnection(null); // Acquire the connection

        var cmd = con.CreateCommand();
        cmd.CommandText = "select top 1 msg from #foo"; 
        var msg = cmd.ExecuteScalar().ToString();

        this.Connections.Connection.ReleaseConnection(con); // Release the connection