SSIS not working with temp tables

Received this fine error message during ETL?:
The metadata could not be determined because statement ” in procedure ” uses a temp table.

If you are working on SSIS 2012, then it uses system stored procedure sp_describe_first_result_set to fetch the metadata of the tables and it does not support temporary tables. But you can go for other options like table variables and CTEs which are going to work fine.
In my case, since I’m a bit stubborn I looked for another solution and found a few:



Solution 1:
An option is to use a SET FMTONLY ON command in front of your actual query to send a fake “First result set” to SSIS with your correct column structure. So you can do something like:

SET FMTONLY ON
select 0 as a, 1 as b, 'test' as C, GETDATE() as D
SET FMTONLY OFF

select a, b, c, d from ##TempTable

When SSIS runs sp_describe_first_result_set, it will return the metadata and column names of your FMTONLY command, and won’t complain about not being able to determine the metadata of your temp table because it won’t even try.



Solution 2:
And the best solution is this one in my opinion, simply provide SSIS the metadata up front like:

EXEC [dbo].[usp_SSIS_DataSet] @Date = '2012-01-01'
WITH RESULT SETS
(
(
[DateID] [int] NOT NULL,
[Sales] [decimal](16, 4) NOT NULL
)
)

NOTE: Don’t know why, but syntax wise it needs the round brackets twice.
You can use this in your OLE DB Source component as a query (or in a variable).








Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s