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?
Connection Manager
toTRUE
? See if this helps: stackoverflow.com/questions/5631010/…