Using Temp Tables in Ssis Package Development

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 — even though you have specified the same connection. Each task builds its own connection. 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.