Professional SQL Server™ 2005 Integration Services

6.3. 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. 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. Figure 6-11

Each task using a connection will build its own connection using the properties provided by the Connection Manager ...