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)
))
'

sp_whoisactive for SQL Azure

There is a small portion of sp_Whoisactive code which gives the results about the Agent Jobs and their steps. If we comment that code then it can be used for SQL azure instances also.

The complete code can be found at :https://drive.google.com/file/d/0B-Ezzyr3ZkV7UmNWVTVzcXVDdVE/view?usp=sharing

Below is the code snippet to comment:

IF @output_column_list LIKE '%|[program_name|]%' ESCAPE '|'
 AND @output_column_list LIKE '%|[additional_info|]%' ESCAPE '|'
 AND @recursion = 1
BEGIN
  ;

 DECLARE @job_id UNIQUEIDENTIFIER;
 DECLARE @step_id INT;

 DECLARE agent_cursor CURSOR LOCAL FAST_FORWARD
 FOR
 SELECT s.session_id
  ,agent_nodes.agent_node.value('(job_id/text())[1]', 'uniqueidentifier') AS job_id
  ,agent_nodes.agent_node.value('(step_id/text())[1]', 'int') AS step_id
 FROM #sessions AS s
 CROSS APPLY s.additional_info.nodes('//agent_job_info') AS agent_nodes(agent_node)
 WHERE s.recursion = 1
 OPTION (KEEPFIXED PLAN);

 OPEN agent_cursor;

 FETCH NEXT
 FROM agent_cursor
 INTO @session_id
  ,@job_id
  ,@step_id;

 WHILE @@FETCH_STATUS = 0
 BEGIN
   ;

  BEGIN TRY
    ;

   DECLARE @job_name SYSNAME;

   SET @job_name = NULL;

   DECLARE @step_name SYSNAME;

   SET @step_name = NULL;

   SELECT @job_name = REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(j.NAME, NCHAR(31), N'?'), NCHAR(30), N'?'), NCHAR(29), N'?'), NCHAR(28), N'?'), NCHAR(27), N'?'), NCHAR(26), N'?'), NCHAR(25), N'?'), NCHAR(24), N'?'), NCHAR(23), N'?'), NCHAR(22), N'?'), NCHAR(21), N'?'), NCHAR(20), N'?'), NCHAR(19), N'?'), NCHAR(18), N'?'), NCHAR(17), N'?'), NCHAR(16), N'?'), NCHAR(15), N'?'), NCHAR(14), N'?'), NCHAR(12), N'?'), NCHAR(11), N'?'), NCHAR(8), N'?'), NCHAR(7), N'?'), NCHAR(6), N'?'), NCHAR(5), N'?'), NCHAR(4), N'?'), NCHAR(3), N'?'), NCHAR(2), N'?'), NCHAR(1), N'?'), NCHAR(0), N'?')
    ,@step_name = REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(s.step_name, NCHAR(31), N'?'), NCHAR(30), N'?'), NCHAR(29), N'?'), NCHAR(28), N'?'), NCHAR(27), N'?'), NCHAR(26), N'?'), NCHAR(25), N'?'), NCHAR(24), N'?'), NCHAR(23), N'?'), NCHAR(22), N'?'), NCHAR(21), N'?'), NCHAR(20), N'?'), NCHAR(19), N'?'), NCHAR(18), N'?'), NCHAR(17), N'?'), NCHAR(16), N'?'), NCHAR(15), N'?'), NCHAR(14), N'?'), NCHAR(12), N'?'), NCHAR(11), N'?'), NCHAR(8), N'?'), NCHAR(7), N'?'), NCHAR(6), N'?'), NCHAR(5), N'?'), NCHAR(4), N'?'), NCHAR(3), N'?'), NCHAR(2), N'?'), NCHAR(1), N'?'), NCHAR(0), N'?')
   FROM msdb.dbo.sysjobs AS j
   INNER JOIN msdb..sysjobsteps AS s ON j.job_id = s.job_id
   WHERE j.job_id = @job_id
    AND s.step_id = @step_id;

   IF @job_name IS NOT NULL
   BEGIN
     ;

    UPDATE s
    SET additional_info.modify('
        insert text{sql:variable("@job_name")}
        into (/additional_info/agent_job_info/job_name)[1]
       ')
    FROM #sessions AS s
    WHERE s.session_id = @session_id
    OPTION (KEEPFIXED PLAN);

    UPDATE s
    SET additional_info.modify('
        insert text{sql:variable("@step_name")}
        into (/additional_info/agent_job_info/step_name)[1]
       ')
    FROM #sessions AS s
    WHERE s.session_id = @session_id
    OPTION (KEEPFIXED PLAN);
   END;
  END TRY

  BEGIN CATCH
    ;

   DECLARE @msdb_error_message NVARCHAR(256);

   SET @msdb_error_message = ERROR_MESSAGE();

   UPDATE s
   SET additional_info.modify('
       insert <msdb_query_error>{sql:variable("@msdb_error_message")}</msdb_query_error>
       as last
       into (/additional_info/agent_job_info)[1]
      ')
   FROM #sessions AS s
   WHERE s.session_id = @session_id
    AND s.recursion = 1
   OPTION (KEEPFIXED PLAN);
  END CATCH;

  FETCH NEXT
  FROM agent_cursor
  INTO @session_id
   ,@job_id
   ,@step_id;
 END;

 CLOSE agent_cursor;

 DEALLOCATE agent_cursor;
END;