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:
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.
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).