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?

#### Links This takes up: - [Using temporary tables in SSIS flow fails - Stack Overflow](https://stackoverflow.com/questions/37945772/using-temporary-tables-in-ssis-flow-fails) - [Is it possible to use a temp table in a data flow source?](https://dba.stackexchange.com/questions/73132/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](https://stackoverflow.com/questions/25558513/do-i-lose-my-temporary-tables-when-changing-tasks-in-an-ssis-package) - [How does SSIS manage closing connections? Can I force it? - Stack Overflow](https://stackoverflow.com/questions/13733692/how-does-ssis-manage-closing-connections-can-i-force-it) - [Is it possible to use OleDbConnections with the Script Component?](https://stackoverflow.com/questions/15792632/its-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?](https://wenleicao.github.io/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?](https://stackoverflow.com/a/78196761/11154841) 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?](https://dba.stackexchange.com/a/338130/212659) :

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?](https://dba.stackexchange.com/q/339257/212659) 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.