I have stored procedure , which has temp table in it.. When i executing this stored procedure directly it's running good... But when i calling this stored procedure from SSIS package getting error message saying that
" The metadata could not be determined because select statement in procedure uses a temp table. "
Can you please suggest...
Add the following in your stored procedure:
SET NOCOUNT ON;
SET FMTONLY OFF;
SSIS (and SSRS) try to execute the query to return the metadata by using FMTONLY. This cannot be utilized when there are temp tables. Forcing the value to OFF prevents that from occurring - and therefore SSIS/SSRS will then execute the stored procedure to get the metadata.
Note: if your stored procedure takes a long time to execute - it will take a long time to get the metadata. If this is the case then you should consider rewriting the process so you are not utilizing a temp table. One option could be to have a prior task that truncates and populates a permanent table - that is then utilized in this procedure to extract the data.
My approach to this is to check for FMTONLY and when it is set, return a dummy table to satisfy SSIS/SSRS
It worked after i created permanent tables instead of temp tables.