Problems Using Temporary Tables

I am declaring a temp table in control flow via Execute SQL task. I then want to use that table as a source in a data task, keep it alive going back to control flow and use it as a destination in another data task following. I am having trouble just getting the first data flow to access that temp table.

In my Execute SQL task, I'm creating a simple temp table. After executing that task, I can head to the data task and change my OLE DB Source to point to that temp table, but I recieve an error about an invalid table name when I attempt to map columns or hit "OK."

Is there something else I need to do to use that temp table as a source or can I only reference it through SQL queries?

Thank you.

I have found them to be unreliable and problematic to use in SSIS. The meta-data issues during dev. for example are a real pain, and the difficulty of controlling the lifetime of the temp tables is another.

I am now using table variables where I previously used temp tables. Table variables are typically faster for small row counts but slower with greater row counts. Unfortunately, I am using them for greater row counts and my sql statement went from 5 1/2 minutes to 8 1/2 minutes for about 250,000 rows.

|||How would I go about doing that though? I would like to try it out even though I'm aware of the limited success.
|||

Well, you've been warned :)

It's my understanding that the only thing you can do to SSIS to specifically facilitate the use of temp tables is to set the "retain same connection" property to "true" for the connection that is used to create the temp table, and then use that connection for all references to the table.

Ken

|||

Although you can use temporary tables in tempdb (#MyTempTable) by setting RetainSameConnection, you might prefer creating a temporary table in an ordinary database (MyTempTable), without setting RetainSameConnection:

Create the table ahead of time. Truncate it before using it on each invocation.

Cheers/Kristian

|||

kenambrose wrote:

Well, you've been warned :)

It's my understanding that the only thing you can do to SSIS to specifically facilitate the use of temp tables is to set the "retain same connection" property to "true" for the connection that is used to create the temp table, and then use that connection for all references to the table.

Ken

Right, I've seen this answer before, and actually did this. It allowed me to see the temp table after executing the task (as #TempTable_0283817, or something similar) but when I select it, I recieve an error.

I will reply later with the specific error in a few hours when I can get to the SQL box.
|||Here is the error message that pops up when I try to map columns to the temp table I chose from the drop down.

TITLE: Microsoft Visual Studio

Error at Data Flow Task [OLE DB Destination [340]]: An OLE DB error has occurred. Error code: 0x80040E37.

Error at Data Flow Task [OLE DB Destination [340]]: Opening a rowset for "[dbo].[#TestTable_000000002219]" failed. Check that the object exists in the database.

ADDITIONAL INFORMATION:

Exception from HRESULT: 0xC02020E8 (Microsoft.SqlServer.DTSPipelineWrap)

BUTTONS:

OK

|||

Yes that works of course but then the SSIS user context needs "create object" and "write" permissions on the source data server (unless you are willing to suffer the performance disaster of heterogenerous joins). Bad and/or impossible for most extraction scenarios.....

Ken

|||That's what the staging database is for in most extract scenarios, I find that even creating tempdb tables on the source systems are seldom OK.

Either way, by having an authorized user creating the table before SSIS runs, it's enough to give the SSIS context insert/delete rights.

Cheers/Kristian|||

Kristian Wedberg wrote:

That's what the staging database is for in most extract scenarios, I find that even creating tempdb tables on the source systems are seldom OK.

Either way, by having an authorized user creating the table before SSIS runs, it's enough to give the SSIS context insert/delete rights.

Cheers/Kristian

When you say "staging database" are you referring to an object in the SSIS package or is that just a general term?

Basically, what I want to do is run through the entire input file, validate it, and record any errors I find before inserting any DB records.
|||

In my use of temp tables and the OLE DB Source or Destination I have resorted to using the Advanced Editor where you can create the columns and mappings "by hand". An easy way to see how to set them up is to create a table in your dev db that looks like your temp table. Set up the source or destination using that table and map your columns. Then open up the Advanced Editor.

One thing I found with temp tables. Of course you know about the RetainSameConnection setting. I was using the same connection to create the temp table, fill it, then open it so that I could write to another table on the same connection. Where I write to another table on the same connection I had to create a new separate connection and use it to write to the other table. Otherwise it seemed the viability of the temp table was uncertain when I run a second parallel data flow task where that temp table would be used as well.

Put another way, I use one connection to create the temp table, fill it and read from it. I do not use that same connection to write or read from any other tables. This may not be effecient but it was the only way I could get things to work in parallel.

Hope this helps,
David Martin

|||We are loading data from several source systems simultaneously. Each system generates a set of identical files using the Raw Data File destination. All extracts share common load procedures that pick up the generated Raw Data files through an expression variable. It's a pretty slick way around temp tables and seems to work pretty fast. Would be nicer if there was a Recordset source and we didn't have to create the file on the server but for now there isn't one. You may want to look at that approach as a way around temporary tables.|||

Martin is absolutely right. Raw files are akin to temp tables.

He is correct that there is no recordset source (and with good reason) but you can build one yourself as explined here: http://blogs.conchango.com/jamiethomson/archive/2006/01/04/2540.aspx

You can't append to a recordset destination unfortunately. If you think that would be nice functionality to have then vote for it here: http://lab.msdn.microsoft.com/productfeedback/viewfeedback.aspx?feedbackid=6831c0b9-ef74-4132-a137-b073a950d3fd

-Jamie

|||I meant "staging database" as a general term, i.e. it's often useful for the SSIS part to have access to a database where it can write and read arbitrary tables, even if that database is different from both your source database and your target database.|||

<< we didn't have to create the file on the server >>

That's fine if:

a: Your package runs local to your source data (very rare I expect)

or

b: You do not need to reference temp data structures in the queries that populate the raw data files.

But when you need the functionality of temporary data structures when working with queries that reference remote source data, it's either heterogenerous joins (death to performance) or table variables.

Ken