Wrestling with Temp Tables in SSIS Data Sources

When handing data we can make use of temporary tables to aid with separation or performance. However, they don’t always play nice with Integration Services packages.

If we set a source to call a procedure returning the contents of a temporary table we’ll see an error like below:

The process is unable to determine the metadata for an object that doesn’t exist. Similarly, it causes an issue when calling the proc sp_describe_first_result_set on the server itself:

EXEC sp_describe_first_result_set
	@tsql = N'EXEC dbo.OrderReportData;';

The metadata could not be determined because statement ‘SELECT * FROM #Results’ in procedure ‘OrderReportData’ uses a temp table.

Options

There are a few approaches which we could take to alleviate the issue:

• Rework the procedure – to avoid the use of temporary tables, for example using one large query, or combining with sub queries or CTEs. This may have a performance impact on the SQL side
• Move the processing into the package – using existing features within SSIS to combine, filter, and manipulate the data, removing logic from the procedure. This will impact performance of the SSIS package
• Stage the results – put the results into a persistent table which the package can reliably reference

My preferred option is the third one. It has minimal impact on both the SQL code and SSIS package. This means not only less effort, but less chance of impacting behaviour or performance as a result.

Staging the data

Staging the data is simple. Instead of outputting via a SELECT we will INSERT into a new staging table. This table will then be used by the package to read the data.

Firstly let’s create a staging schema for the table if we don’t already have one:

IF NOT EXISTS (
	SELECT 'exists'
	FROM sys.schemas
	WHERE [name] = 'stg'
)
BEGIN
	EXEC ('CREATE SCHEMA [stg]
		AUTHORIZATION [dbo];');
END

Now we can create our staging table which should mirror the output from the procedure:

CREATE TABLE [stg].[ReportResults] (
	OrderID INT,
	OrderDate DATETIME,
	CustomerName VARCHAR(100),
	ProductName VARCHAR(100),
	OrderQuantity INT
);

Next up, its updating the procedure. Firstly don’t forget to clear the contents of the temporary table before you populate it:

TRUNCATE TABLE [stg].[ReportResults];

Then we need to finish the proc inserting the data which would normally be returned from our temporary table. A basic example would be:

INSERT INTO [stg].[ReportResults] (
	OrderID,
	OrderDate,
	CustomerName,
	ProductName,
	OrderQuantity
)
SELECT *
FROM #Results;

Modifying the package

Now that we’ve got a procedure which is staging the data, we need to call the procedure to populate the table, and then read the data as separate processes.

First up we’ll add a new Execute SQL Task step prior to the data flow where we’re reading it. This will now call the procedure and populate the staging table:

Finally we need to change the source reader to use the staging table instead of calling the procedure:

As we now have a table in the database, this will mean no ambiguity with the results. This will allow the package to read the metadata and we can map this in the data flow.

Wrap up

In this post we’ve seen how the use of temporary tables in procedures can impact SSIS when trying to use their output as a data source.

We’ve highlighted a few ways to combat this challenge, and worked through my preferred option to separate the processing of the data from the reading of it.

This approach requires some modification on both the SQL and SSIS sides of the process, but the changes are relatively minor. Due to this, the potential for performance impact is minimised compared to the alternatives.