Welcome to EMC Consulting Blogs Sign in | Join | Help

SSIS Junkie

SSIS: Using temporary tables

There have been a few posts on the SSIS MSDN Forum asking how one can create and use SQL Server temporary tables within SSIS packages.

 

Temporary tables are tables that are created within a session and live for the life of that session or until all sessions using it have ended. They are denoted by either having a hash (e.g. #mylocaltemptable) at the start of the name indicating the temp table can only be accessed in the session that created it or two hashes (e.g. ##myglobaltemptable) indicating that the temp table can be accessed by all sessions.

 

My answer was always "Set RetainSameConnection=TRUE on the connection manager and set DelayValidation=TRUE on all tasks that use the temp table. Setting RetainSameConnection=TRUE ensures that the temp table does not disappear when the task that creates it has completed. Setting DelayValidation=TRUE ensures that the tasks do not try to check that the table exists before it is created.

 

Ostensibly all of that is true and it is enough information to execute a package. However, I have to admit I never actually tried it out. This week at the PASS Summit Sujata (sorry, I can only remember her first name) came to the SQL Lounge; where Simon Sabin and I are were doing our best to help out at the 'Ask The Experts' sessions; asking the very same question about temp tables and we set about coming up with a solution. I didn't have a laptop with me however Simon did and it was mainly he that worked it out.

 

It is possible to execute a package that uses temp tables but the idosyncracies of the process are not at execution-time, rather at design-time. Picture the scenario. It is easy to drag on an Execute SQL Task that creates a temp table and then execute that task, but the temp table needs to exist after that task has finished executing in order that other tasks that need to use that temp table can discover their external metadata and therefore be created. Therein lies the problem - its similar to the proverbial chicken and egg situation.

 

Happily there is a way around this, it is just a little bit more laborious than the process would normally would be. Follow the steps below and you won't go far wrong.

 

My imaginary scenario is one where I want to create a temporary table using an Execute SQL Task and then consume it using an OLE DB Source component in a data-flow task. Here's what you have to do:

  1. Create an OLE DB Connection Manager to point at a SQL Server database .
  2. Set connection manager property RetainSameConnection=TRUE. As explained earlier this is a fundamental step in getting this to work.
  3. Drag on an Execute SQL Task and configure it to create a global temp table.
  4. Copy the same CREATE statement from the Execute SQL Task, paste it into SQL Server Management Studio, and create the table from there. This gets around the problem of the table not existing when it is initially created by a SSIS package at design-time. It has to be a global temp table in order that it can be accessed by your package at design-time.
  5. Create a data-flow task that consumes your global temp table in an OLE DB Source component.
  6. Set DelayValidation=TRUE on the data-flow task.

At this stage you will have a package that can be executed successfully. It will create a global temp table and consume it. If you want the temp table to be scoped locally instead of globally then there are a few extra steps that you need to do:

  1. On the SSIS menu in BIDS, select 'Work Offline'.
  2. Change SQLStatementSource property of Execute SQL Task to create a local temp table instead of global.
  3. Change SQLCommand property of OLE DB Source to use the local temp table instead of global. You will have to use the Properties window to do this because if you try to do it in the component editor it will try and validate the external metadata.

 

And there you have it. Proof that you can use temporary tables with SSIS. There is another, probably quicker, way of doing this but I think this is the best method because it educates as to what the root of the problem is and how it is worked around.

You can download a demo package from here. Provided you have a SQL Server instance available at localhost then the demo package will execute without editing it.

 

-Jamie

 

UPDATE, 19th March 2008: This blog entry has been up here for a while and has received a lot of comments. It has become evident that some people are now looking at this mechanism and using it where it isn't necassary and I want to maybe try and "pull the reins in" a bit. In all my time developing SSIS packages I've never actually used this technique. Typically if I want to land data somewhere so that it could be used in another package then I use a raw file, I'm not a fan of dropping data into a database just for the sake of it.

Of course, all situations are different so I would never recommend a carte blanche approach of always/never use this technique. Its another "string to your bow" as it were but always assess whether or not its the correct technique for you.

Comments

 

Professional Association for SQL Server (PASS) SIG said:

November 30, 2006 4:23 PM
 

Professional Association for SQL Server (PASS) SIG said:

November 30, 2006 4:23 PM
 

Anil said:

Doesn't work !!!

August 8, 2007 3:30 AM
 

Lindsay said:

Jamie, I found this post out on the MSDN SSIS forum; exactly what I was looking for, and it's executing perfectly.  You've saved me a lot of headaches from trying to do a workaround!

August 14, 2007 5:51 PM
 

jamie.thomson said:

Hi Lindsay,

Good news, thanks for letting me know.

Anil,

I would suggest that it does work given that Lindsay has managed to get it working. perhaps you should try again?

-Jamie

August 14, 2007 5:58 PM
 

Lindsay said:

Forgot to add, once you have the mappings done, you do not need to keep creating the temp table in Management Studio first.  Even though you'll see the error symbol on your OLEDB destination, your package will still execute (I'm guessing that's what the delay validation is).

August 14, 2007 6:08 PM
 

jamie.thomson said:

Lindsay,

Spot on. Normal behaviour is that validation occurs twice:

(1)When the package spins up, prior to ANY of the executables executing and

(2)Immediately prior to the executable executing.

By setting DelayValidation=TRUE you're basically turning off the first validation.

-Jamie

August 14, 2007 6:22 PM
 

Lindsay said:

Jamie, I was trying to do this again in a different package.  I mocked the successful package that I have, but my new one is not working.  When I execute the package, I can see in the data flow that it is trying to validate the temp table, which is the last step in the flow.  I have double-checked that I have RetainSameConnection=TRUE and DelayValidation=TRUE.  

My package creates a temp table successfully in an Exec SQL Task.  The next step is a data flow, which retrieves records from a staging table, does an unpivot, some simple converting of fields, two lookups, and then unsuccessfully inserts into my temp table.  

My error I get is: [OLE DB Destination [16936]] Error: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER.  The AcquireConnection method call to the connection manager "sqldev\bidev.tempdb" failed with error code 0xC001A004.  

Do you know what could be wrong?

October 4, 2007 7:18 PM
 

Saurabh said:

Jamie.

Your approach did solve my problem to some extent. But my situation is somewhat like this:

I have an existing DTS package which I am migrating to SSIS. We have a Dynamic Properties task which we run before development/debugging the DTS package and it sets the Source and Detsination Object to existing database tables.

In the actual workflow, just before the Data Pump task we have another dynamic properties task which sets the Source and Detsination Object to temporay tables. (##xyz).

This way if one wants to have a look at the column mapping at design mode, all he needs to do is run the first dynamic properties task.

I guess your approach will not work in the above scenario. Any pointers or suggestions will be of immense help.

October 16, 2007 8:55 PM
 

Ken said:

Jamie -

    Thank you for posting this article!  I am having an issue with getting this to work - probably my inexperience with temp tables in SQL Server!

    I create a global temp table in SS 2005 using something like:

    CREATE ##SomeTable (SomeValue int)

    I then created the execute SQL task to create the temp table and fill it:

    CREATE #SomeTable (SomeValue int)

    INSERT INTO #SomeTable (SomeValue) VALUES (1)

    When I try to create the OLE DB Connection, I try selecting from the temp table such as:

    SELECT * FROM #SomeTable

    I have DelayValidation=True and DelayValidation=True but I cannot get the task to save during design time.

    Do you have any suggestions on what I am doing incorrectly?

    Thanks!

December 18, 2007 4:20 AM
 

jamie.thomson said:

Ken,

What eror message do you get?

December 18, 2007 1:22 PM
 

Chris said:

Hi Jamie

I've got a strange one - when I execute a stored procedure from an OLE DB Command transformation, where the sp takes a parameter and RetainSameConnection=TRUE and DelayValidation=TRUE are set, I get the error

"Syntax error, permission violation, or other nonspecific error"

If I take out the param or set RetainSameConnection=FALSE on the connection, all is fine again?

I wondered if you've come across this?

Cheers,

Chris

December 19, 2007 5:28 PM
 

jamie.thomson said:

Weird. Sorry Chris, never seen that one.

December 19, 2007 7:07 PM
 

vince said:

Is there a way to see the timestamp (date and time) for when the temporary table is created?

Thanks

Vince

January 22, 2008 9:43 PM
 

Andy said:

Hi Jamie,

Fantastic blog. It's as good as the books I bought to learn SSIS.

I've found this temp table solution works perfectly for the scenario you've described, where you're using the temp table to be consumed by an oledb SOURCE. For what I'm trying to do, in order to avoid a physical staging table, I need to write data to a temp table DESTINATION. I've found that I can't select my temp table in the destination the same way I can in a source. Is there something I'm missing?

January 31, 2008 2:20 AM
 

jamie.thomson said:

Andy,

Not sure. Try toggling the ValidateExternalmetadata property on the destination.

-Jamie

January 31, 2008 3:22 PM
 

Andy said:

I got the temp tables working for an oledb DESTINATION. The only way I could get it to work was to create the table in the destination editor by clicking the "NEW" button next to the select table dropdown. Then the table name [##stringmap] would be in the dropdown and I could move on to the "columns" screen.

Thanks again for the article.

February 1, 2008 2:00 AM
 

Ravi said:

How do I do this on sql2000 DTS package?  I am trying to insert an existing text file into a #temptable.  Creation of #temptable is precedent task to insert task.  The dts transformation errors with 'Invalid object name #temptable'. I tried with global ##temptable.  It errors with same message.

Thanks

February 1, 2008 5:22 PM
 

Doug said:

I have a SP that created a temp table then select data into it and from there I do a select statement off the temp table to get my results.  Does anyone know how to get SSIS to view my SP?  I have tried a few different ways and can't get anything to work.

February 7, 2008 7:06 PM
 

Jeremy Giaco said:

Try adding this before the stored proc or query:

SET FMTONLY OFF;

exec dbo.usp_SomeProcedure;

The designer turns this ON so as not to actually manipulate data or anything at design time.  Test it in a query window.  

SET FMTONLY ON

Select * From SomeTable

SET FMTONLY OFF

Select * From SomeTable

Just be careful where you use it, as it will actually run everything in your query instead of estimating the resultset.  

February 13, 2008 7:49 PM
 

Jeremy Giaco said:

February 13, 2008 7:53 PM
 

Mike said:

Hi Jamie.

Thanks for the article. I am trying this out now and could use a little help. I want to populate the table from inside of a data flow where the records needed are the result of a merge and finally a conditional split. What is the best way to do this? I assume it would have to be with an OLE DB Command, but I do not know how to setup the output to go into the temp table.

Overall problem:

Basically, I have a data validation data flow that excludes records based on various criteria and I want the end result of my validation data flow to be the input to another data flow process that transforms the data some more (can add details here if needed) and inserts the records into a database.

Any help would be appreciated.

Thanks, Mike

March 19, 2008 7:15 PM
 

jamie.thomson said:

Mike,

Use an OLE DB Destination to insert into a table.

-Jamie

March 19, 2008 8:20 PM
 

Mike said:

Thanks Jamie.

I got it to work using an OLE DB Destination with a SQL Statement. Sometimes, I am a little slow... :-)

Mike

March 19, 2008 9:30 PM
 

Srikanth said:

Thanks Jamie,

I was trying to get this for a long time.

Any ways my package is like this,

I get a Postional file from which i have to generate Multiple flat files based on a particular field.

to do this i am trying to populate the data from flat file into a ##table and use For Each ADO enumerator to loop through the field,which i want.

here the package is not failing,but inside the temp table only the last row is persisted and all other rows are not existent.

any solutions for this will be appreciated.

cheers

srikanth katte

April 24, 2008 12:02 PM
 

jamie.thomson said:

Ooo...not sure about that I'm afraid Srikanth. Sounds like it SHOULD work but without being there I can't really say for sure!

-Jamie

April 24, 2008 12:09 PM
 

Chronos said:

Hi Jamie,

I've got a problem with retainsameconnection property of dynamic OleDB connection. I've set up a couple of connection and use a couple of Execute SQL Task.

The process I need in one Execute SQL task use 'A' connection and the other use 'B' connection but I need to validate both of the process, so if one (especially the last process) fail, all processes will fail.

With only one fixed OLEDB connection I'm able to do this with double BEGIN TRAN, COMMIT TRAN, and ROLLBACK TRAN for each connection, and retainsameconnection property of both connection set to True.

But when I set the connectionstring property of the connection dynamically within a script task within foreach loop container (loading many connections I need to use). It always execute the first initial connection string. When I set the retainsameconnection property of the connection to False then all things work fine, except for the transaction block. Without the transaction block all went ok, but I loose validation process I need for both connection.

I already tried to set transaction to required to no avail.

1. I run the distribution transaction service

2. I set security configuration of MSDTC to allow Network DTC access and all of the component, choosing no authentication required.

without this setting I've got error "The partner transaction manager has disabled its support for remote/network transactions.", with this setting I've still got error "The transaction has already been implicitly or explicitly committed or aborted" (I didn't use any transaction block)

July 29, 2008 8:00 AM
 

Chronos said:

Never mind that, I've solved the problem by using Parent Package to execute Child Package.

August 13, 2008 5:12 AM
 

Carl said:

I spent a while trying to get around this problem. The easiest solution is actually to make sure you SET NOCOUNT ON at the top of your script.

This does work as long there is only one SELECT to return results. The other can SELECT INTO or be part of an INSERT INTO.

September 2, 2008 3:57 PM
 

Sunil said:

I did try but my requirement is using temp tables with parameters and found that this combination does not work with these steps any leads???

September 4, 2008 1:05 AM
 

VNNK said:

Hello

We are using SQL 2005 Fuzzy grouping and it runs fine in our test

environments and the same package is taking hell lot of time in production

database. The database size is same in both environments.

I ran the trace and found it is taking very long time in tempDB cursor…

Cursor is very simple inserting into a table with while loop. It takes 4K to

process 1 min in the beginning and gradually it takes 2K records in a min and

finally it will never completes. Where as in the Test environments same

tempdb cursor takes 20 to 30 sec to process all records. Total records are

around 150K.

Only Difference I see is in ….Temp DB  file groups in production we have 30

+ FG …in test environments two file groups.

Here is the cursor.

--############################

DECLARE FGCursor CURSOR LOCAL

FOR

SELECT _key_out FROM

[tempdb]..[##FLOut_080909_12:46:51_544_babf6eb0-7d77-4d7d-bef1-42563f3c4e60]

GROUP BY _key_out

ORDER BY sum(_score) DESC

DECLARE @key_out INT

OPEN FGCursor

FETCH next FROM FGCursor

INTO @key_out

WHILE @@fetch_status = 0

BEGIN

INSERT INTO

[tempdb]..[##FGOut_080909_12:46:51_544_babf6eb0-7d77-4d7d-bef1-42563f3c4e60]

SELECT * FROM

[tempdb]..[##FLOut_080909_12:46:51_544_babf6eb0-7d77-4d7d-bef1-42563f3c4e60]

o

WHERE _key_out = @key_out and

not exists (SELECT 1 FROM

[tempdb]..[##FGOut_080909_12:46:51_544_babf6eb0-7d77-4d7d-bef1-42563f3c4e60]

i

WHERE i._key_in = o._key_in or i._key_in = o._key_out)

FETCH next FROM FGCursor

INTO @key_out

END

DEALLOCATE FGCursor

--########################################

September 11, 2008 5:32 PM
 

Nick Maroudas said:

Hi jamie,

Thanks for the post, got me out of a real jam. Have been battling with local temp files in SSIS for ages, a global temp file never crossed my mind.

Nick

September 12, 2008 12:43 PM
 

CoastalData said:

Jamie, how, then, do you handle this error message:

"The raw adapters do not support image, text, or ntext data"??????

I've been going round and round and round and round!!!!!!!! I MUST get past this and complete my Upsert!!!!

October 14, 2008 6:26 PM
 

Shuaib said:

Hi

I am new to SSIS and need some help.

My requirement:

If any column value changed in the table (primary key exists in the table), that need to be updated into another table.  The destination table is on remote server.  

I created SSIS package and tried to use temp table but I am unable to access that table.  

SSIS package details

1.  In the control flow, I Used SQL Execute Tasks to create temp table

2.  In the data flow task,

     I specified the table name in OLEDB Source.

     I specified the temp table name in the destination to dump the data from source to destination.

3.  In the control flow, I used SQL task to populate data to final destination table.

4.  When execute the package getting object doesnt exists error.

Please some one help on this and let me know if there any other options to do this.

Thanks in advance

October 17, 2008 2:19 AM
 

jamie.thomson said:

Shuaib,

This blog entry explains exactly how to use temporary tables in a dataflow. Did you follow the instructions exactly?

-Jamie

October 17, 2008 9:14 AM
 

SSIS Junkie said:

I have been having an email back-and-forth with Jon Jaques which began with us discussing various limitations

October 17, 2008 10:13 AM
 

CozyRoc said:

At the end of the article Jamie explains he doesn't see the need to use temporary tables, in case he wants to pass data flow from one package to another. He uses raw file source and destination. It is worth mentioning there is even better solution available on the market, which is faster than the raw file. Instead of first writing data to file and then pulling it back from that file, the alternative solution streams data directly from one data flow to another.

You can find more about in the following blog post:

http://cozyroc.wordpress.com/2007/11/04/comparing-performance-of-a-raw-file-vs-cozyroc-data-flow-source-and-destination/

October 20, 2008 6:25 PM
 

Shuaib said:

Thanks Jamie....  I have created global temp table and it is working.   Is there any alternate to avoid using temp table ?

October 24, 2008 12:48 AM
 

jamie.thomson said:

Shuaib,

It depends on what you are trying to do.

-Jamie

October 24, 2008 8:58 AM
 

Ramachandran said:

Jamie,

I am able to create the table but then what I want to do is take the data from a flat file and then put it into this temp table. But I am not able to do that. As per you said it works if it is a global temporary table. But then once i complete the package and then delete the table I cannot delete the global temp table. The moment i delete it and try to execute the package within the designer (sql 2005) i throws up an error. Should i run the package outside or can i run it from my designer. The moment I delete the gloabal temporary table and then i try to run the package the error I get is that the designer says it cannot find the table. Any help?

November 13, 2008 5:10 PM
 

Mudit said:

Hello,

thanks for your posting. Its very informative.

I tried to replicate the task but I am taking some addtional steps also for the process.

Here it goes;

1) create a OLEDB connection pointing to SQL ServerDB

2) retainconnection=TRUE.

3) introduce ExecuteSQL task.

BODY:

create table ##temp01(id int)

GO

insert into ##temp01(id)

(

Select distinct Id from table)

GO

4) RUN

create table ##temp01(id int)

GO

under SSMS

5) I add a data flow task & set DelayValidation = TRUE

6) Add a OLE DB Source.

BODY:

SELECT * FROM #temp01

7) under SSMS DROP TABLE #temp01

8) Add a FLAT FILE destination and map it.

Is this correct? though it works.. But its not straight forward.

Secondly, For working with temp tables, It seems I need to create task for global temp table and next, need to modify EXECUTE SQL TASK and OLEDB SOURCE along with enabling WORK OFFLINE.

BTW, I am trying to export result of a temp table to a different format say flat file/ ACCESS/EXCEL.

I started with BCP. But figured out that, I can use Sproc ,as query,in BCP.

However it seems, i have to use Global temp table for using BCP. It doesn't work with local temp tables.

The thing being if using SSIS, i have to first create a global temp table and later mmodify it for local temp table then in that case, BCP seems better option.

Please advise.

Thanks

November 14, 2008 4:15 PM
 

Aneel said:

Is it possible to have a select...into and a select statement inside the same Execute SQL task...

November 21, 2008 5:39 PM
 

jamie.thomson said:

Aneel,

Yes.

Why not try it and find out?

-Jamie

November 23, 2008 8:14 PM
 

JDS said:

Thanks guys.

This worked perfectly for me.

November 25, 2008 8:34 PM
 

Aneel said:

I had a problem when using Execute SQL task to run a .sql. The .sql contains two sets of statements one that does a select..into and the other a select. I have mapped the result set of the Execute SQL to a variable. When I started executing, it throw me an error saying disconnected result set not handled or some thing like that. And that is when I have posted the query.

After that I have realized that my select...into and select were under two batches(had a "go" in between) and that was the problem. Sorted that out...

But now, I have problem in that, I have a child package which contains (among other tasks) a file system task to make a copy of a file. When I  use a execute package task to execute this child package, I am getting the following error

"Error: The task "Copy the Template File to a temproray file" cannot run on this edition of Integration Services. It requires a higher level edition. "

But when I run the Child Package alone, it is executing with out any issues...

Any suggestions.

November 26, 2008 7:27 PM
 

jamie.thomson said:

Aneel,

You've only installed the workstation tools, not SSIS tiself.

-Jamie

November 26, 2008 7:38 PM
 

Aneel said:

Jamie, thanks for the reply. BTW, what does that mean and what should I do to fix it.

November 26, 2008 8:11 PM
 

Aneel said:

Jamie,

Need your help on this. I am trying to load data into Excel file from SQL Server table.

After I did this, I wanted to transpose the rows in Excel to columns which I am doing using Script Task.

Now, after I do this, I am doing a copy of this modified excel(Source) to a location and then rename the Source.

But the package is failing at this File System Task level saying that the file is being used by another process. I have verified that the Script Component is doing the clean up work and releasing the file handle.

So what could the probable reason for this. Is the Dataflow task the culprit. Any pointers as to how to resolve this.

Thanks for the Help.

Aneel

November 26, 2008 8:18 PM
 

Aneel said:

Jamie,

Just Realized I was using a send mail task and this mail task takes the generated Source Excel as attachment. But this Send Mail task was failing and so the file handle is not being released by the Send Mail Task on failure.

So is there a way to force Clean up on the Send Mail Task.

thanks in advance

Aneel

November 26, 2008 8:36 PM
 

jamie.thomson said:

Aneel,

Not that I know of. Sorry.

-Jamie

November 26, 2008 9:01 PM
New Comments to this post are disabled

This Blog

Syndication

Powered by Community Server (Personal Edition), by Telligent Systems