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

Question

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?

Answer 1

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
    )
)

Answer 2

The temp table will confuse SSIS. To get around the issue, write your code like this:

Use Database
Begin
    Exec('Create Table ##Table(Field Integer)') With Result Sets None;
    Exec('Select Field From ##Table') With Result Sets
    ((
        Field Integer
    ));
End

This allows the use of the temp table and provides the metadata to SSIS.

Answer 3

I know this is an old post, but I thought I would offer another solution that I have found helpful not just in SSIS, but in Management Studio Import Wizard, PowerPivot, etc.. If you don't need to index your temp table, you could always use table variables instead. As long as you use the "set nocount on" hint at the beginning, you should be able to use this in your SSIS data flow source. Your new code should look something like this...

set nocount on

declare @Checksums table (DBName sysname, CheckSum bigint)
--Code that loads the Checksum table here
select DBName, CheckSum from @Checksums

Answer 4

The answer to the question is "No, it cannot be done"

A temp table is dropped as soon as you step out of just one DFT item to the next. Leaving the Data Source item where you create a temp table will already drop it again. Yet, if you work with a Script Component, you can work around this and keep the temp table in memory inside the connection manager by not closing but releasing it at the end of "Script Component 1" so that the temp table is available in "Script Component 2" once you acquire the released connection again, see 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?.

I also found remarks that assure this viewpoint, see Using temporary tables in SSIS flow fails:

The asker's remark:

I am using temporary tables because i dont want to use OleDbCommand objects to perform updates on a row by row basis. I drop new rows into a temp table and then issue a merge to update the staging table - i am happy that they disappear after the job is finished

The remark on this by Panagiotis Kanavos:

They disappear when the connection closes, not when the job is finished. The connection will close when you move from one dataflow to the next, or when you move from the SQL script that created the table to then next script or flow. Which is why the package is broken - there is no table there to bind to or validate.

To be sure about this, I tested this with two Script Components after each other, and when I released the connection and acquired it again, the tmpTable came back into the "tempDB" database, fully filled. With a Data Source as a source, you do no longer have the choice to release the connection, and instead, it gets closed, as we read from the question itself. If it released the connection instead, this question would not pop up at all, and there is no way to switch from closing to releasing the connection in the settings of the Data Source item.

Workaround 1: Make and fill the temp table in a Script Component and pass its data over the Script Component output

There is a way to make a temporary table in a Script Component and loop over the Row object to pass it as the output to the next DFT item. This is not an answer to the question since you cannot do this with the mere Data Source item. And even if you take the Script Component instead, the temp table itself will not survive in the tempDB database or at least seems to get hidden in between the Script Components. But it is kept in memory inside the connection itself and pops up again once you acquire the connection in the next Script Component. For the full code, see again the link from above: 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?.

To see how you can create and fill the temp table, see: 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?.

Workaround 2: Make a temp table in the Control Flow, then you can take it up in one Data Flow Task item before it gets dropped (or you redo the workaround 1 there and keep it alive with a Script Component by releasing the connection without closing it)

If you begin with making a temp table in a Control Flow "Execute SQL Task", you can reach this temp table in the DFT.

The bad news: You can reach it only one time for just one DFT item. If you need it again and again inside the same DFT, see "Workaround 1".

The good news: There is no need to deal with variables, stored procedures or other workarounds. Take the "tempdb" system database as the ADO.NET connection on project level and set the "RetainSameConnection" connection property to True. All of your temporary tables are always just saved in that "tempdb" system database.

See a guide on this with many screenshots at How to create a temporary table in SSIS control flow task and then use it in data flow task? - Stack Overflow.

SSIS can reach temporary tables on the "tempdb" database and deal with these tables as if they were not temporary. The temporary tables stay alive within the Control Flow until the run stops or until you step from one Data Flow Task (DFT) item to the next unless you follow workaround 1, or if you stop the debugging, see How to see temp table created by code in sql server?. You can choose them as a normal destination or source in the editor while you are still planning if you create them during your SSIS work.