Ssis Package Not Wanting to Fetch Metadata of Temporary Table

SSIS Package not wanting to fetch metadata of temporary table

Using WITH RESULT SETS to explicitly define the metadata will allow SSIS to skip the sp_describe_first_result_set step and use the metadata that you define. The upside is that you can use this to get SSIS to execute SQL that contains a temporary table (for me, that performance helped a lot); the downside is, you have to manually maintain and update this if anything changes.

Query sample (stored procedure:)

    EXEC ('dbo.MyStoredProcedure')
    WITH RESULT SETS
      (
        (
            MyIntegerColumn INT NOT NULL,
            MyTextColumn VARCHAR(50) NULL,
            MyOtherColumn BIT NULL
        )
      )

Query sample (simple SQL:)

EXEC ('
    CREATE TABLE #a 
      (
        MyIntegerColumn INT NOT NULL,
        MyTextColumn VARCHAR(50) NULL,
        MyOtherColumn BIT NULL
      ) 
    INSERT INTO #a 
      (
        MyIntegerColumn,
        MyTextColumn,
        MyOtherColumn
      )
    SELECT 
        1 AS MyIntegerColumn,
        ''x'' AS MyTextColumn,
        0 AS MyOtherColumn

    SELECT MyIntegerColumn, MyTextColumn, MyOtherColumn
    FROM #a')

WITH RESULT SETS
    (
        (
            MyIntegerColumn INT NOT NULL
           ,MyTextColumn VARCHAR(50) NULL
           ,MyOtherColumn BIT NULL
        )
    )

I don't use temp table in SSIS

Background

What's likely happening here is that the table does not exist at the moment. Temporary tables come in two variants: local and global.

A local temporary table, uses a name with a single sharp/pound/hash/octothorpe prepended to it i.e. #TEMP. The only query that can use that instance of the temporary table is the one that creates it. This is why the advice across the internet says you need to set RetainSameConnection to true to ensure the connection that created the table is reused in the data flow. Otherwise, you're at the mercy of connection pooling and maybe the same connection is used in both places, maybe not and believe me that's an unpleasant bit of randomness to try and debug. The reason for the DelayValidation on the data flow is that as the package starts, the engine will validate that all the data looks as expected before it does any work. As the precursor step is what gets the data flow task into the expected state, we need to tell the execution to only validate the task immediately before execution. Validation always happens, it's just a matter of when you pay the price.

A global temporary table is defined with a double sharp/etc sign prepended to it, ##TEMP. This is accessible by any process, not just the connection that created it. It will live until the creating connection goes away (or explicitly drops it).

Resolution

Once the package is designed (the metadata is established in the data flow), using local temporary table is going to work just fine. Developing it though, it's impossible to use a local temporary table as a source in the data flow. If you execute the precursor step, that connection will open up, create the temporary table and then the connection goes away as does the temporary table.

I would resolve this by the following steps

Copy the query in your Execute SQL Task into a window in SSMS and create your local temporary table as a global temporary table, thus ##TEMP.
Create an SSIS variable called SourceQuery of type String with a value of SELECT * FROM ##TEMP;
Modify the "Data access mode" from the OLE DB Source from "SQL Command" to "SQL Command from Variable" and use the variable User::SourceQuery
Complete the design of the Data Flow
Save the package to ensure the metadata is persisted
Change the query in our variable from referencing ##TEMP to #TEMP
Save again.
Drop the ##TEMP table or close the connection
Run the package to ensure everything is working as I expect it.

Steps 2, 3, and 6 in the above allows you to emulate the magician pulling the tablecloth out from underneath all the dishes.

If you were to manually edit the query in the data flow itself from ##TEMP to #TEMP, the validation fires and since there is no #TEMP table available, it'll report VS_NEEDSNEWMETADATA and likely doesn't let you save the package. Using a variable as the query source provides a level of indirection that gets us around the "validate-on-change"/reinitialize metadata step.

Using temporary tables in SSIS flow fails

Several suggestions to your case.

Set RetainSameCoonection=true. This will allow you to work safely with TEMP tables in SSIS packages.
Would not use ExecuteOutOfProcess, it will increase your RAM footprint since every Child pack will start in its process, and decrease performance - add process start lag. This used in 32-bit environments to overcome 2 GB limit, but on x64 it is no longer necessary.

Child package execution does not inherit connection object instances from its Parent, so the same connection will not be spanned across all of your Child packages.
SSIS Packages with Temp table operations are more difficult to debug (less obvious), so pay attention to testing.