Using Temp Tables in SSIS Package Development

In the Import Column example, you created a physical table in your production database to stage a list of files. 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. Seems easy, right? It is, but there is a trick and a little surprise in the default behavior of one of the components. Figure 6-10 shows a quick example of a package with 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.

Figure 6-10

Notice that the drop portion of the package failed. If you review the package progress tab, the error message reports that the table doesn't exist. Obviously both of these Execute SQL tasks do not share the same connection — even though they share the same graphical connection. You'll notice in the regular property window of the OLE DB connection (as shown in Figure 6-11) that there is a property RetainSameConnection that is set to "FALSE" as a default.

Figure 6-11

Each task using a connection will build its own connection using the properties provided by the Connection Manager for that property. A temp table can only live in one connection and it is automatically destroyed when that connection is closed. However, if you change this property to "TRUE," both Execute SQL tasks will share the same connection and both will be able to use the temp table. This trick can also be useful if 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.