SQL Server Integration Services is a tidy bit of kit and an absolute must have on any SQL Server developer or administrator’s toolbelt. However, it is as frustrating as it is sublime, with many obfuscated error messages, quirky behaviours and downright oddness. I ran into one such quirk recently while setting up a Data Flow Task using an OLE DB Source that loaded its data from a Stored Procedure, specified by a package Variable. Everything was working fine until I identified that I needed to do something in the stored procedure that resulted in my using a temporary table. As soon as I updated the procedure and attempted to refresh the columns in my OLE DB Source, I was hit with the following error:
Msg 11525, Level 16, State 1, Procedure My_Stored_Procedure_Name, Line 1
The metadata could not be determined because statement 'My SQL Statement Here;' uses a temp table.
So why does this happen, even when I’ve defined the type of the column in my procedure?
As it turns out, SSIS 2012 uses a system stored procedure named sp_describe_first_result_set to look up output metadata for any given command. A quick Google led me to this (nearly 2 year old) MS Connect issue, which confirms that this procedure (as well as others) do not support temporary tables. Now it seems like this restriction has been green lit for the final release version, so that means we need a workaround. Fortunately, there are several solutions to this, and which one you use is totally up to you and the requirements of your particular situation.
Rather than using a temporary table, you can declare a Table Variable in your procedure and use that. Funnily enough, there is no issue with using a table variable as part of your result set.
Check out this article for an overview of table variables vs temporary tables.
You can also use a CTE. In my case, my temporary table was a tally table, so a Recursive CTE would also have fitted my requirements.
If you really have to use a temporary table, you can work around this by declaring your result set when you call the EXEC/EXECUTE command to specify the columns and types that your stored procedure returns.
EXEC [dbo].[usp_sync_dim_Targets_Stage]
@DaysBack = 2
WITH RESULT SETS (
(
CustomerID int,
DateStamp date,
StartDate date,
EndDate date,
AccountMappingId int,
ChannelId int,
TargetTypeId int,
TargetType varchar(50),
AggregateType varchar(50),
TargetBreakdownID int,
TargetBreakdown varchar(50),
TargetValue float
)
)
The downside of this approach is that any changes to the underlying columns will require you to update your stored procedure, then update the command to reflect these changes in the result set, and finally refresh your OLE DB Source to update the metadata.
Microsoft have stated that this functionality was left out due to time and resource constraints due to the fact that temp tables are defined at execution time and not parse/bind time when the other methods are deduced. In such, I doubt there’s much chance of this being added in future, so the best bet is to use one of the workarounds above.
Let me know in the comments below if you’ve run into this error or if you’ve found another way to deal with this.