Using Temp Tables in SSIS Package Development

Friday, March 23, 2007

Often while working in a SSIS package you will require to temporary hold your data in a staging table in one of the Data Flow Tasks and then in another task you will require to fetch data from the staging table, perform transformations and load it and delete the staging table.

It means you create a physical table in your production database to stage data. But in a production environment, you may not want to create and destroy objects in the production database and might prefer to use temp tables instead. This seems easy, in fact it is, but it requires a trick and to modify the default properties of the components. Let us see what to do in this regard.

In the figure you have two Execute SQL tasks. The Create Temp Table task executes a SQL command to create a temporary table named #tmpMyData. The Drop Temp Table task executes a SQL command to drop table #tmpMyData.

If you execute this package, you will notice that the drop portion of the package failed. The package progress tab will report the error message that the table doesn't exist. This is because both of these Execute SQL tasks do not share the same connection, rather they just share the same connection manager. Each task builds its own connection from the connection manager. So when the first task is finished, temp table is destroyed and the second task creates a new connection.

To fix this in the regular property window of the OLE DB connection there is a property RetainSameConnection that is set to "FALSE" as a default. Changing it to "TRUE" is our trick and will solve the problem.

By changing this property to "TRUE," both Execute SQL tasks will share the same connection and both will be able to use the temp table.

You can use this trick for performance in SSIS packages also in the case you are going to be performing a task requiring a connection within a loop. Otherwise, imagine how many openings and closings are going to occur during that loop.


Jamie Thomson said...

"This is because both of these Execute SQL tasks do not share the same connection — even though you have specified the same connection"

No you haven't. You've specified the same connection manager - there's a subtle but important difference.


May 7, 2007 at 6:15 AM
freak said...

Thanks Jamie, you are right. I have edited the post accordingly.

April 18, 2008 at 3:49 AM
Navodita said...

Hey Jamie,
I am not getting out the way to Use temporary table created by Execute SQL Task in Data Flow Task.

My Need is to Parse the XML File and save it in temp table for further logic. By when i am using OLEDB Destination it is not showing temp table name there.

I am fresher in SSIS and ur help will be appreciated.


December 30, 2008 at 4:41 PM
SQL Like said...

We also have a trick in place for using it with the temp table, its here

December 9, 2009 at 11:53 AM
Abhinav Dhiman said...

I am having exactly the same issue as Navodita is facing.

December 13, 2012 at 5:28 AM

Post a Comment



2009 ·Techy Freak by TNB