Learn more about SQL Server tools

 

Tutorials          DBA          Dev          BI          Career          Categories          Events          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Events

DBA    Dev    BI    Categories

 

How to create and use Temp tables in SSIS


By:   |   Read Comments (32)   |   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:





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

View all my tips











More SQL Server Solutions




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


Get free SQL tips:

*Enter Code refresh code     



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

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

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

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 05, 2015 - 2:24:09 PM - SERGIO ARIAS Back To Top

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

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

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

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

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

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

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 Back To Top

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

Very nice artical Brady...


Friday, May 02, 2014 - 6:39:22 AM - elon musk Back To Top

saved my day!

 

thanks matey!


Friday, November 01, 2013 - 10:12:19 AM - Darek Back To Top

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

Osm one.. Worked perfectly fine


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

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 Back To Top

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

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

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 Back To Top

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

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 Back To Top

Great piece of information , it is highly appreciated.

 


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

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

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

Thanks for sharing this, its very helpful for beginner.

 


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

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 Back To Top

 

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 Back To Top

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 Back To Top

Brady,

Congratulations for the article.

 

I am doing to test this!

Regards.


Friday, December 07, 2012 - 10:09:20 AM - David Back To Top

Great article!


Friday, December 07, 2012 - 9:36:54 AM - Kit Back To Top

 

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


Friday, December 07, 2012 - 7:58:33 AM - Hilary Huber Back To Top

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


Learn more about SQL Server tools