Sql-server – Is it possible to use a temp table in a data flow source

sql serversql-server-2012ssis-2012

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?

Best Answer

These steps helped me:

  1. Write the final result set into a table.
  2. Script that table as CREATE into a new New Query Editor Window.
  3. Remove everything except the open and close brackets that define the columns.
  4. Wrap that into another pair of brackets.

  5. Recompose the calling of your SP from

    exec p_MySPWithTempTables ?, ?

into

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