How to create and use Temp tables in SSIS

By:   |   Comments (37)   |   Related: More > Integration Services Development


I'm trying to use a temp table in an SSIS package. It seems like everything is working correctly until I try to query the temp table. What am I doing wrong?


Creating temp tables in SSIS seems like a straight-forward process using the Execute SQL Task, however there are a couple of properties that must be changed. In this tip, we'll walk through creating a simple temp table in SSIS.

Creating Sample SSIS Package

First, I will drag an Execute SQL Task into my Design view and rename it Create Temp Table:

Creating temp tables in SSIS

Next, I will right click and edit and choose my connection and SQLStatement:

choose my connection and SQLStatement

(The SQL statement used in this example is below)

IF OBJECT_ID('tempdb..##tmpTeams') IS NOT NULL
    DROP TABLE ##tmpTeams
    CREATE TABLE ##tmpTeams
        Team VARCHAR(255),
        Mascot VARCHAR(255),
  State VARCHAR (2)
       ('Auburn', 'Tigers', 'AL'),
       ('Alabama', 'Crimson Tide', 'AL'),
       ('Mississippi', 'Rebels', 'MS'),
       ('Louisiana State', 'Tigers', 'LA'),
       ('Mississippi State', 'Bulldogs', 'MS'),
 ('Arkansas', 'Razorbacks', 'AR'),
 ('Texas A&M', 'Aggies', 'TX'),
 ('Georgia', 'Bulldogs', 'GA'),
 ('Tennessee', 'Volunteers', 'TN'),
 ('Vanderbilt', 'Commodores', 'TN'),
 ('Florida', 'Gators', 'FL'),
 ('South Carolina', 'Gamecocks', 'SC'),
 ('Missouri', 'Tigers', 'MO')

Next, I will drag a Data Flow task into my Design view, rename it Query and drag the precedence constraint between the two:

rename it Query

For the Data Flow task we are going to query the temp table and export the results to a database table. Right click the Data Flow task and choose Edit. Drag a OLE DB Source and a OLE DB Destination task into the Design view.

Right click the Data Flow task

To avoid errors when configuring the OLE DB Source we need to create the temp table first using SSMS. In SSMS run the following statement to create a global temp table:

        Team VARCHAR(255),
        Mascot VARCHAR(255),
  State VARCHAR (2)

Once the table has been created, let's go back into our SSIS package. Right click OLE DB Source and choose Edit. Choose your data source and choose SQL command for the Data access mode dropdown. In the SQL command text we will need to create our SQL statement:

SQL command text

Hit OK on the OLE DB Source window and right click OLE DB Destination and choose Edit. Choose your datasource and choose "Table or View - fast load" from the Data access mode dropdown. I already created a table called AlabamaTeams that I will use to display the data and will use this name in the dropdown for the Name of the table or the view.

Choose your datasource

Once finished configuring the OLE DB Destination hit OK. We are now ready to execute the package. Hit F5 to begin or choose Debug, Start Debugging from the Menu bar:

Start Debugging from the Menu bar

After executing the package, an error will occur on the Query task:

an error will occur on the Query task

If we look at the Progress tab we can see that the reason this error occurs is because after the temp table is created in the "Create Temp Table" task it is deleted. The "Query" task searches for the table but can't find it.

Create Temp Table

Fixing the Issue

To fix this issue, we will need to change a few properties on the tasks and connection manager. The first property is a connection manager property. If you right click the OLEDB Connection Manager and choose properties you will see a property called RetainSameConnection. This must be set to "True". RetainSameConnection means that the temp table will not be deleted when the task is completed.


The second property we must change is the Execute SQL Task property. If you right click on each task (Create Temp Table and Query) and choose properties you will see a property called DelayValidation. This must be set to "True". DelayValidation means that the task will not check if the table exists upon creation.

the Execute SQL Task property

Once we have set both properties to "True", execute the package again. The package should be successful this time:

The package should be successful

If we switch back over to SSMS and query the table, AlabamaTeams, we should return a result set:

switch back over to SSMS and query the table

**Note: I'm using global temp tables (##tmpTeams) instead of local temp tables (#tmpTeams) because when I create the table using SSMS the first time it uses a different session (SPID) therefore when I try to configure my OLE DB Source it cannot find the temp table and gives the following error:

OLE DB Source

With that said, only global temp tables should be used. Also, make sure when you are creating your global temp tables that the table name does not interfere with any other global temp tables that may be created using other processes.

Next Steps
  • I would assume that when creating a temp table inside a SSIS package that other tasks in the package will use the temp table, therefore I suggest adding another task that will drop the temp tables at the end of the package. For example, DROP TABLE ##tmpTeams.
  • Check out this tutorial to learn more about temp tables.

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Brady Upton Brady Upton is a Database Administrator and SharePoint superstar in Nashville, TN.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Comments For This Article

Thursday, February 13, 2020 - 3:09:55 PM - Cordell Prediger Back To Top (84436)


Thursday, November 14, 2019 - 3:54:11 PM - Priya Pandey Back To Top (83095)

Thanks for the article. It was great.. It was exactly the same that i needed. 

Monday, February 18, 2019 - 6:35:57 PM - Prabhash Back To Top (79058)

It's†a great article with precise steps. Thanks for sharing it.

Tuesday, March 28, 2017 - 6:39:54 PM - AD Back To Top (53830)

 Hi All, 

My SSIS package is loading data into a global temperory table at Temp DB, the package runs successfully. But I cannot access the temperory table from my SSMS, like if I query: Select C1 From ##Temptable, the table does not exist. Probably whats happening is as the session ends my global temp is dropped. Alternate solution can be using  a permanent table in temp db instead of a temp table but I don't have permissions to use a permanent table, I must use only temp tables.

Any suggestions on this?


Monday, September 12, 2016 - 12:15:52 PM - Kamala Raghunathan Back To Top (43305)

 Hello Brady,   I have created a global temp table in the ssis pkg and have loaded data into it in the execute sql module.   Next in a Data flow task I am trying to select from this global table  and then need to push it to a permenant table.   This is process I need to execute.

The global temp table gets created and once it gets built I can do a select in Aqua sql editor and see the records.   But inside the data flow -- n the the OLEBD source,   it does not recognize the existence of the table and fails.

I have set the connection manage property -- Retain same connection = Yes

In the Exec sql module and the Data flow  --- the Delay validation = yes 

In the data flow task   --- OLEDB Source properties -- ValidateExternalMetadata = false.

As I was testing this,  I got this to work one time,  but had to make some changes and it has stopped working.  How and what can I do to get this

to work.   I need to work with global tables a lot and this is my first attempt at this.

Appreciate your help and ideas.   I read in the blog "Using global Temp tables in SSIS",  that ther may be a bug using this process.




Tuesday, May 31, 2016 - 3:20:38 PM - Kris Maly Back To Top (41583)

Is there no way to create Glopbal Temp Table creation in SSIS without going to SSMS?

Tuesday, May 31, 2016 - 2:22:28 PM - Kris Maly Back To Top (41582)

I enjoyed readingtI enjoyed reading this article and recommend others.

Thanks for educating the community and appreciate your volunteership.


All Steps are fine.


In the middle you are asking us to create Global Temp table instead of this can we not create this table using Exec SQL Task or Exec SQL Statement Task then do the rest of your steps?

I would appreciate your response.

 Steps like

Control Flow->Exec SQL Task (Create Global Temp Tbl) -> DataFlow Task -> OLEDB Source->OLEDB Dest


Please please please keep writing such articles with examples, I love.

Thanks a bunch

Thursday, March 24, 2016 - 2:41:28 PM - Jaes W Overley Back To Top (41050)

I have been trying to figure out how to use SSIS to query a database on one server to populate a table in a database on another server. The key things to keep in mind from this post are the following.

  • Set your source connection property RetainSameConnection = True
  • Use a global temp table with two hash symbols ##tempTable
  • Set your data flow property DelayValidation = True
  • Create your global temp table using SQL Server Management Services (SSMS) to resolve your data flow mappings

Once I followed your example and double-checked that I did everything above, this post was exactly what I needed and helped me finally move forward on my project. I bookmarked this post and shared it with my team. Thank you!

Thursday, November 5, 2015 - 2:24:09 PM - SERGIO ARIAS Back To Top (39028)

I didn't know we can use temp tables in SSIS, great tip and great explanation !

Monday, September 28, 2015 - 4:29:36 PM - Dave Holt Back To Top (38774)

Hello Brady!

Great Document! Thank you. Question: It says I cannot convert be Unicode and non Unicode string data types. Is there a simple work around for this using your example I am using VS 2012.


Thanks again.

Dave Holt 

Tuesday, February 17, 2015 - 12:06:48 AM - Tharun Santosh Back To Top (36257)

Thank you very much, it really helped me to work on my requirement. :)

Monday, November 24, 2014 - 1:12:27 PM - NZ Back To Top (35385)

Amazing, thank you so much for this. I used the trick to create a flat file. Your steps are clear and work perfectly.

Tuesday, November 18, 2014 - 3:16:48 PM - Bill Gibbs Back To Top (35328)

this is familiar, and always a concern BI tools - in SSRS it was always necessary to drop WITH RECOMPILE into Stored Procs to ensure that the tmp table would be available for .RDL File Datassource


Sunday, September 21, 2014 - 10:27:45 PM - honkcal Back To Top (34647)

i wanna know when we should use the temp table in ssis ,and what is the reason why we must use the temp table in ssis.

any way ,thank u very much for your sharing

Friday, August 15, 2014 - 8:37:03 AM - Bina Back To Top (34147)

Brillant article. Very helpful.


Thank you for posting this.... really awesome trick. i was trying so many things and this finally worked and solved the issue.




Friday, August 8, 2014 - 10:37:15 AM - Venkata Back To Top (34060)

Do I have to run the create query in SSMS before I run the package every time..? It works for the first time and package executed. Later on I tried this and my package has compilation errors because it is not finding the Global temp table.

Tuesday, June 17, 2014 - 10:03:55 AM - Ankit Shah Back To Top (32280)

Very nice artical Brady...

Friday, May 2, 2014 - 6:39:22 AM - elon musk Back To Top (30591)

saved my day!


thanks matey!

Friday, November 1, 2013 - 10:12:19 AM - Darek Back To Top (27362)

To avoid any clashed between global temp tables it's sufficient to attach a GUID generated via T-SQL in SSMS (NEWID()) to the name of the table like, say, ##tempTable_E9C93C1EC9A54673BF176FB0F7B40F66. Probability of clashes - next to nothing.

Friday, July 26, 2013 - 2:50:54 AM - saumil Back To Top (26020)

Osm one.. Worked perfectly fine

Friday, May 31, 2013 - 4:38:30 PM - Charlie Biggs Back To Top (25223)

Hi Brady,

I have it working with Global Temp Tables, but I real need it to work on Local Temp Tables.   I am dropping the Global Temp Tables right after I am done to act like they was Local Temp Tables. I agree with Prasanta, I was hoping that he would response with the trick to getting it to work.




Charlie J.


Friday, May 31, 2013 - 2:05:13 PM - Brady Back To Top (25217)

Stephen, Charlie,

Have you tried using global temp tables instead of local. I could not get it to work using local either.

Friday, May 31, 2013 - 12:43:14 AM - Charlie Biggs Back To Top (25208)

I am having the same issue as Stephen.  I am using SSIS 2012.

Friday, May 10, 2013 - 12:09:24 PM - Stephen Back To Top (23880)

I would like to use local temp tables instead of global. The problem is I still get an error because the data flow task cannot find my local temp table. Not sure why this is, I have RetainConnection set to true. And DelayValidation is also true.

i.e - SQL TASK:

CREATE TABLE #JobValidation (

event_id numeric(12,0)

,job_no varchar(20)

,contract int

,priority int




INSERT INTO #JobValidation (event_id, job_no, contract, priority) VALUES ( ?, ?, ?, ?)

 NB: The parameter mapping was setup by first creating a global temp table to avoid the common metadata problem.


The error received:

SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80040E37.

An OLE DB record is available.  Source: "Microsoft SQL Native Client"  Hresult: 0x80040E37  Description: "Invalid object name '#JobValidation'.".

Tuesday, January 29, 2013 - 6:41:22 AM - Akshay Arora Back To Top (21768)

I completely agree with Prasanta, we can use local temp table in SSIS.

Monday, January 28, 2013 - 9:51:28 PM - Prasanta Behera Back To Top (21763)

Hi Brady,

Even we can use local temp tables(#) in ssis package.

After your first run with the above settings or all set with the meta data,

you go to the OLEDB Source properties -> then change the property "SQL Command" to select data from local temp table (select * from #temp1)

then change the CREATE and INSERT statement to local temp table.


I think, using local temp table is better than using global temp table in ssis package.





Wednesday, January 2, 2013 - 6:07:49 PM - Duncan Back To Top (21239)

Great piece of information , it is highly appreciated.


Thursday, December 27, 2012 - 10:02:43 AM - sal Back To Top (21159)

I've never used temp tables in SSIS but if I do I will definitely give this a go. thanks for posting.

Friday, December 14, 2012 - 12:36:25 AM - Vinod Back To Top (20952)

Good point to share and I appreciate the way it has explained. 

Tuesday, December 11, 2012 - 5:55:13 AM - Vijay Sarade Back To Top (20870)

Thanks for sharing this, its very helpful for beginner.


Monday, December 10, 2012 - 12:16:47 AM - Rajesh Sunkariya Back To Top (20843)

Thanks for sharing this article with us. i will try to use this facility in my next SSIS Package Development.

Saturday, December 8, 2012 - 9:14:23 AM - Sherlee Back To Top (20824)


Congratulations! Nice article and very helpful for me because  I'm currently exploring and learning how to use SSIS in my projects.

Thanks for sharing this one. 

Friday, December 7, 2012 - 12:49:47 PM - Amar Sale Back To Top (20811)

Amazing article on using Temp tables in SSIS. Helped me in preparing for my interviews. Will try it... Thank you very much !!! 


Amar Sale

Friday, December 7, 2012 - 11:08:33 AM - Junior Galv„o - MVP Back To Top (20809)


Congratulations for the article.


I am doing to test this!


Friday, December 7, 2012 - 10:09:20 AM - David Back To Top (20806)

Great article!

Friday, December 7, 2012 - 9:36:54 AM - Kit Back To Top (20805)


Thanks for sharing this article. Just tried and realise how it works. 

Friday, December 7, 2012 - 7:58:33 AM - Hilary Huber Back To Top (20801)

Thanks for the article, it is very detailed and easy to follow. I am going to try this!

get free sql tips
agree to terms