SSIS not able to discover Meta data while executing Stored Procedures

SSIS internally uses a system stored procedure sp_describe_first_result_set to find the result set of a particular SQL Batch.

This is done to create the column mappings with the appropriate data types.

But when you use a temp table in the stored procedure it fails to retrieve the data failing with errors like:

Msg 11526, Level 16, State 1, Procedure sp_describe_first_result_set, Line 1
The metadata could not be determined because statement 'SELECT * FROM #Temp_Table' in procedure 'SSIS_Test_SP' uses a temp table.

Consider the following SP:

CREATE PROCEDURE SSIS_Test_SP
AS
BEGIN
 SELECT 1 AS EmpId
  ,'Name' AS EmpName
 INTO #Temp_table

 SELECT *
 FROM #Temp_Table
END

This is a simple stored procedure which results in 1 row with 2 columns.
When you execute our internal sp to see the output we will get an error.


sp_describe_first_result_set @tsql= N'EXEC SSIS_Test_SP'

Msg 11526, Level 16, State 1, Procedure sp_describe_first_result_set, Line 1
The metadata could not be determined because statement 'SELECT * FROM #Temp_Table' in procedure 'SSIS_Test_SP' uses a temp table.


To get around the problem we just need to add information about the result set:

EXEC SSIS_Test_SP
WITH RESULT SETS((
   EmpID INT
   ,EmpName VARCHAR(200)
   ))

This tells us that the SP results the following with the mentioned data types.
Now our Internal SP should work :

sp_describe_first_result_set @tsql = N'
EXEC SSIS_Test_SP
WITH RESULT SETS

(
(
EmpID INT
,EmpName VARCHAR(200)
))
'