Tag Archives: #temp

Database wordcloud

How to use Temp tables in an SSIS package

If you have been using a query which utilizes temp tables but now you want that query to run as an SSIS package it’s going to need a bit of reworking.

Just adding the query as an Execute SQL Task in Visual Studio won’t work unfortunately if you are using, for example, SELECT * INTO to create the temp tables.

The First step to reworking the query will be to dedicate an Execute SQL Task to creating the temp tables your query uses, defining columns and data types etc. and then populating those tables with INSERT INTO rather than SELECT INTO.

For the following we will assume only one output temp table is required for the process which will be as follows:

  • A temp table to store data will be created.
  • A query will be used to populate this table.
  • This populated temp table will then become the source from which data will be pulled.

STEP 1.

In SSMS connect to what will be the source server and enter the statement to create the required temp table as a global temp table i.e. prefixing the table name with ##. Creating the table as a global temp table at this stage avoids errors when configuring the OLE DB Source in Visual Studio as it allows the table to be visible to the SSIS package.

STEP 2.

In Visual Studios drag a new Execute SQL Task into the control flow and name it, Create Temp Table.

Image showing Execute SQL Task Create Temp Table

Double click on the task to bring up the Execute SQL Task Editor window.

Image showing the Execute SQL Task Editor Window

Step through the process for creating a connection in the connection option and enter the following for the SQL statement option replacing yourTempTable with the name of your temp table.

IF OBJECT_ID('tempdb..##yourTempTable') IS NOT NULL
   DROP TABLE ##yourTempTable

 

Below this statement enter the same statement to create the global temp table as you used in SSMS.

STEP 3.

Next in the Create Temp Tables Properties, the window to the bottom right, for the section Execution under the option DelayValidation set the value to True. In STEP 1 the temp table is created and visible to Visual Studio, but when the SSIS package is run in the future the table won’t be there to be validated prior to the package running. Without setting this property to true the package would seek to confirm the existence of the temp table and would error when it determined the temp table does not exist stopping the package from running.

Image of Create Temp Table Task Properties showing DelayingValidation option set to True

STEP 4.

Next at the bottom of the design view window in Visual Studio the current source connection should be displayed. Left click on the source connection and the properties window should change focus to connection properties. Set the RetainSameConnection option to True, this allows the global temp table to remain visible to SSIS package during the package’s execution by maintaining the session.

Image showing the Connection Properties window with the RetainSameConnection option set to true

STEP 5.

Drag a Data Flow Task onto the Control Flow workspace and rename it Query and drag the precedence constraint between the Create Temp Table and Query.

Image showing OLE DB Source creation process

Double left click on the Query Task.

From Other Sources drag an OLE DB Source into the Data Flow workspace.

Double left click on the OLE DB Source and set the OLE DB Connection Manager to your source server and Data Access Mode to SQL Command.

Image of the OLE DB Source Editor Window

Enter your SQL Command in the box provided e.g.

SELECT * FROM ##yourTempTable

 

Hit OK on the OLE DB Source.

STEP 6.

Set up the destination as you would with any other typical SSIS package.

From Other Destinations drag an OLE DB Destination into the Data Flow workspace.

Then drag the precedence constraint between OLE DB Source to the OLE DB Destination.

Double left click on the OLE DB Destination and choose your destination and set “Table or View – fast load” from the data access mode dropdown. For the “Name of table or view option” enter your intended destination.

Hit OK on the OLE DB Destination.

Finally I would suggest adding another task that will drop the temp tables at the end of the package. For example, DROP TABLE ##yourTempTable.

Conclusion.

At this point you should now be able to run the package successfully and make your own adaptions to this process to fit your future needs.

How to create Clustered and Nonclustered Indexes on a Temp Table

One of the best features of temp tables is that an index can be applied to them.

To clarify temp tables start with #, exist within the tempdb and are accessible within child batches (nested triggers, procedure, exec calls) of the query.

The execution plan can also determine the relevant statistics regarding their operation and suggest means of optimisation and will often suggest applying an index to the table.
Below is a simple example of applying both clustered and nonclustered indexes to the temporary table #Apostle created from a CTE (Common Table Expression).

 

IF OBJECT_ID('tempdb..#Apostle') IS NOT NULL
	DROP TABLE #Apostle;

WITH CTE
AS (
	SELECT 1 AS ID
		,'Simon Peter' AS NAME
	
	UNION
	
	SELECT 2
		,'James'
	
	UNION
	
	SELECT 3
		,'John'
	
	UNION
	
	SELECT 4
		,'Andrew'
	
	UNION
	
	SELECT 5
		,'Philip'
	
	UNION
	
	SELECT 6
		,'Thomas'
	
	UNION
	
	SELECT 7
		,'Bartholomew'
	
	UNION
	
	SELECT 8
		,'Matthew'
	
	UNION
	
	SELECT 9
		,'James'
	
	UNION
	
	SELECT 10
		,'Simon'
	
	UNION
	
	SELECT 11
		,'Thaddaeus'
	
	UNION
	
	SELECT 12
		,'Judas'
	)
SELECT *
INTO #Apostle
FROM CTE

-- CREATE INDEXES
CREATE CLUSTERED INDEX IDX_CLUSTERED_ID ON #Apostle (ID)

CREATE NONCLUSTERED INDEX IDX_NONCLUSTERED_ID ON #Apostle (ID)

 

Microsoft defines clustered and nonclustered indexes as the following:

Clustered

  • Clustered indexes sort and store the data rows in the table or view based on their key values. These are the columns included in the index definition. There can be only one clustered index per table, because the data rows themselves can be sorted in only one order.
  • The only time the data rows in a table are stored in sorted order is when the table contains a clustered index. When a table has a clustered index, the table is called a clustered table. If a table has no clustered index, its data rows are stored in an unordered structure called a heap.

Nonclustered

  • Nonclustered indexes have a structure separate from the data rows. A nonclustered index contains the nonclustered index key values and each key value entry has a pointer to the data row that contains the key value.
  • The pointer from an index row in a nonclustered index to a data row is called a row locator. The structure of the row locator depends on whether the data pages are stored in a heap or a clustered table. For a heap, a row locator is a pointer to the row. For a clustered table, the row locator is the clustered index key.
  • You can add nonkey columns to the leaf level of the nonclustered index to by-pass existing index key limits, 900 bytes and 16 key columns, and execute fully covered, indexed, queries.

Source

To learn more about indexes this is a good video on the topic.