Is it possible to use a temp table in a data flow source?

I have a data flow in a 2012 SSIS package where I'm trying to use a temp table as the data source. I'm using an OLE DB data source with a SQL Command Data access mode.

My code looks like this:

CREATE TABLE #Checksums (DBName sysname, CheckSum bigint)
-- Code that loads the Checksum table here
SELECT DBName, CheckSum FROM #Checksums

In order to get the data source to pull the list of columns I temporarily put the following:

SELECT CAST(NULL AS sysname) DBName, CAST(NULL as bigint) CheckSum

That got the columns created and I was able to map to my destination.

I have ValidateExternalMetadata set to false on the data source and DelayValidation set to true on the data flow.

My code works correctly in SSMS however every time I run the package it errors out with the following:

[OLE DB Source [39]] Error: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available.  Source: "Microsoft SQL Server Native Client 11.0"  Hresult: 0x80004005  Description: "Invalid object name '#qtemp'.".
[OLE DB Source [39]] Error: Unable to retrieve column information from the data source. Make sure your target table in the database is available.
[SSIS.Pipeline] Error: OLE DB Source failed the pre-execute phase and returned error code 0xC020204A.

Is it possible to use a temp table like this in a data source? If so is there a trick I'm missing?

These steps helped me:

• Write the final result set into a table.

• Script that table as CREATE into a new New Query Editor Window.

• Remove everything except the open and close brackets that define the columns.

• Wrap that into another pair of brackets.

-
Recompose the calling of your SP from

exec p_MySPWithTempTables ?, ?

into

exec p_MySPWithTempTables ?, ? with result sets
(
    (
        ColumnA int,
        ColumnB varchar(10),
        ColumnC datetime
    )
)

StackExchange Database Administrators Q#73132, answer score: 3