How can Data Flow destination be a temp table?

I have a series of data flow tasks that I want to output to a temp table. I've set the data source for RetainSameConnection and the Data Flows are DelayValidation. The OLE DB data source inside the Data Flow works fine, but the data destinations don't offer a # or ## as a target. I've tried every destination that sounds logical, without success.

Any pointers? ... Thanks!

I do not get # tables, but that is because the UI cannot be the same connection that created them, so makes sense. Entering the table by hand, using the property grid directly would probably still work for # tables. ## tables get listed in the UI though as expected

Have you selected a connection pointing at your tempdb?

|||

Hi Darren - thanks for responding.

I do use the same connection, and it does persist. I can select from the #temp or ##temp table using a SQL Task.

First, I create either a local temp table (let's call it #temp_local) or global (##temp_global) using an Exec SQL Command. The next task is a Data Flow, with a Source OLE DB being a SQL Query from a different connection than the temp table, and the OLE DB Destination is supposed to be the #temp_table or ##temp_table I just created. However, the dialog box for selecting the tables does NOT include ANY temp tables.

|||Run a profiler trace, and verify the call to list the tables, and check things like the DatabaseID, is it tempdb? It sounds like you have tripped up somewhere. Wrong server? Wrong database? As I explained above I cannot reporduce this behaviour.|||

Looks like I found the answer, thanks to MCS consultant Brian Jackson. Check out http://msdn2.microsoft.com/en-us/library/bb332055.aspx for information on metadata. Evidently GetTableData inconsistently returns temp table meta data, whether it's a global or temp table.

I did find that you can enter the ##temp table name directly into the property box, but not by using the Edit box. And doing that works, kind of, after faking out the column matching dialog on the destination Editor by running it in-stream in debug, after pausing for a breakpoint. That way, the data flow sees the temp table. And the DelayValidation setting retains the pattern matching.

Even so, you're better off building an Expression query and using in-memory transforms, because the inconsistent return of meta data means you end up with data flows that fail for no apparent reason, or consistent error message. Debugging was driving me mad! :-)

Another case of "it's better to re-write, than to reuse" your code sometimes.