Learn more about SQL Server tools

   
   















































How to create and use Temp tables in SSIS

MSSQLTips author Brady Upton By:   |   Read Comments (26)   |   Related Tips: More > Integration Services Development
Problem

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?

Solution

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)
    )
    INSERT INTO ##tmpTeams VALUES
       ('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:

CREATE TABLE ##tmpTeams
    (
        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.


Last Update: 12/7/2012


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

View all my tips


print tip Print  
Become a paid author





join MSSQLTips for free SQL Server tips     



Learn more about SQL Server tools
Post a comment or let the author know this tip helped you.

       All comments are reviewed, so stay on subject or we may delete your comment.

*Name   *Email Notify for updates



       Note: your email address is not published. Required fields are marked with an asterisk (*)


Get free SQL tips:

*Enter Code refresh code     



Monday, November 24, 2014 - 1:12:27 PM - NZ Read The Tip

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 Read The Tip

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 Read The Tip

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 Read The Tip

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.

 

 

THANK YOU!!!!


Friday, August 08, 2014 - 10:37:15 AM - Venkata Read The Tip

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 Read The Tip

Very nice artical Brady...


Friday, May 02, 2014 - 6:39:22 AM - elon musk Read The Tip

saved my day!

 

thanks matey!


Friday, November 01, 2013 - 10:12:19 AM - Darek Read The Tip

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 Read The Tip

Osm one.. Worked perfectly fine


Friday, May 31, 2013 - 4:38:30 PM - Charlie Biggs Read The Tip

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.

 

Thanks,

 

Charlie J.

 


Friday, May 31, 2013 - 2:05:13 PM - Brady Read The Tip

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 Read The Tip

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


Friday, May 10, 2013 - 12:09:24 PM - Stephen Read The Tip

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

)

 

SQL DESTINATION:

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 Read The Tip

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


Monday, January 28, 2013 - 9:51:28 PM - Prasanta Behera Read The Tip

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.

 

Thanks,

Prasanta

 


Wednesday, January 02, 2013 - 6:07:49 PM - Duncan Read The Tip

Great piece of information , it is highly appreciated.

 


Thursday, December 27, 2012 - 10:02:43 AM - sal Read The Tip

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 Read The Tip

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


Tuesday, December 11, 2012 - 5:55:13 AM - Vijay Sarade Read The Tip

Thanks for sharing this, its very helpful for beginner.

 


Monday, December 10, 2012 - 12:16:47 AM - Rajesh Sunkariya Read The Tip

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


Saturday, December 08, 2012 - 9:14:23 AM - Sherlee Read The Tip

 

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 07, 2012 - 12:49:47 PM - Amar Sale Read The Tip

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

 

Amar Sale

Applybi.com


Friday, December 07, 2012 - 11:08:33 AM - Junior Galv„o - MVP Read The Tip

Brady,

Congratulations for the article.

 

I am doing to test this!

Regards.


Friday, December 07, 2012 - 10:09:20 AM - David Read The Tip

Great article!


Friday, December 07, 2012 - 9:36:54 AM - Kit Read The Tip

 

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


Friday, December 07, 2012 - 7:58:33 AM - Hilary Huber Read The Tip

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




 
Sponsor Information