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:
- Create an OLE DB Connection Manager to point at a SQL Server database .
- Set connection manager property RetainSameConnection=TRUE. As explained earlier this is a fundamental step in getting this to work.
- Drag on an Execute SQL Task and configure it to create a global temp table.
- 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.
- Create a data-flow task that consumes your global temp table in an OLE DB Source component.
- 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:
- On the SSIS menu in BIDS, select 'Work Offline'.
- Change SQLStatementSource property of Execute SQL Task to create a local temp table instead of global.
- 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.
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.