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;

Index Information

Also look at 

Table Information Script 

Database Information Script

USE master
GO

IF OBJECT_ID('sp_indexinfo') IS NOT NULL
 DROP PROCEDURE sp_indexinfo
GO

CREATE PROCEDURE sp_indexinfo @tblPat SYSNAME = '%'
 ,@missing_ix TINYINT = 0
AS
WITH key_columns
AS (
 SELECT c.OBJECT_ID
  ,c.NAME AS column_name
  ,ic.key_ordinal
  ,ic.is_included_column
  ,ic.index_id
  ,ic.is_descending_key
 FROM sys.columns AS c
 INNER JOIN sys.index_columns AS ic ON c.OBJECT_ID = ic.OBJECT_ID
  AND ic.column_id = c.column_id
 )
 ,physical_info
AS (
 SELECT p.OBJECT_ID
  ,p.index_id
  ,ds.NAME AS location
  ,SUM(CASE 
    WHEN a.type_desc = 'IN_ROW_DATA'
     THEN p.rows
    ELSE 0
    END) AS rows
  ,SUM(a.total_pages) AS pages
 FROM sys.partitions AS p
 INNER JOIN sys.allocation_units AS a ON p.hobt_id = a.container_id
 INNER JOIN sys.data_spaces AS ds ON a.data_space_id = ds.data_space_id
 GROUP BY OBJECT_ID
  ,index_id
  ,ds.NAME
 )
SELECT OBJECT_SCHEMA_NAME(i.OBJECT_ID) AS sch_name
 ,OBJECT_NAME(i.OBJECT_ID) AS tbl_name
 ,i.NAME AS ix_name
 ,CASE i.TYPE
  WHEN 0
   THEN 'heap'
  WHEN 1
   THEN 'cl'
  WHEN 2
   THEN 'nc'
  WHEN 3
   THEN 'xml'
  WHEN 6
   THEN 'col-store'
  ELSE CAST(i.TYPE AS VARCHAR(2))
  END AS type
 ,i.is_unique AS uq
 ,CASE 
  WHEN is_primary_key = 0
   AND is_unique_constraint = 0
   THEN 'no'
  WHEN is_primary_key = 1
   AND is_unique_constraint = 0
   THEN 'PK'
  WHEN is_primary_key = 0
   AND is_unique_constraint = 1
   THEN 'UQ'
  END AS cnstr
 ,STUFF((
   SELECT CAST(', ' + kc.column_name + CASE kc.is_descending_key
      WHEN 0
       THEN ''
      ELSE ' DESC'
      END AS VARCHAR(MAX)) AS [text()]
   FROM key_columns AS kc
   WHERE i.OBJECT_ID = kc.OBJECT_ID
    AND i.index_id = kc.index_id
    AND kc.is_included_column = 0
   ORDER BY key_ordinal
   FOR XML PATH('')
   ), 1, 2, '') AS key_cols
 ,STUFF((
   SELECT CAST(', ' + column_name AS VARCHAR(MAX)) AS [text()]
   FROM key_columns AS kc
   WHERE i.OBJECT_ID = kc.OBJECT_ID
    AND i.index_id = kc.index_id
    AND kc.is_included_column = 1
   ORDER BY key_ordinal
   FOR XML PATH('')
   ), 1, 2, '') AS incl_cols
 ,p.rows
 ,p.pages
 ,CAST((p.pages * 8.00) / 1024 AS DECIMAL(9, 2)) AS MB
 ,s.user_seeks AS seeks
 ,s.user_scans AS scans
 ,s.user_lookups AS lookups
 ,s.user_updates AS updates
 ,CASE 
  WHEN i.is_disabled = 1
   THEN '[DISABLED]'
  ELSE p.location
  END AS location
 ,i.filter_definition AS filter --requires 2008
 ,INDEXPROPERTY(i.object_id, i.NAME, 'IsDisabled') AS disabled
 ,INDEXPROPERTY(i.object_id, i.NAME, 'IndexDepth') AS depth
 ,INDEXPROPERTY(i.object_id, i.NAME, 'IndexFillFactor ') AS fill_factor
 ,INDEXPROPERTY(i.object_id, i.NAME, 'IsPageLockDisallowed') AS page_lock_disallowed
 ,INDEXPROPERTY(i.object_id, i.NAME, 'IsRowLockDisallowed') AS row_lock_disallowed
FROM sys.indexes AS i
LEFT JOIN physical_info AS p ON i.OBJECT_ID = p.OBJECT_ID
 AND i.index_id = p.index_id
LEFT JOIN sys.dm_db_index_usage_stats AS s ON s.OBJECT_ID = i.OBJECT_ID
 AND s.index_id = i.index_id
 AND s.database_id = DB_ID()
WHERE OBJECTPROPERTY(i.OBJECT_ID, 'IsMsShipped') = 0
 AND OBJECTPROPERTY(i.OBJECT_ID, 'IsTableFunction') = 0
 AND OBJECT_NAME(i.OBJECT_ID) LIKE @tblPat
ORDER BY tbl_name
 ,ix_name

DECLARE @crlf CHAR(2)

SET @crlf = CHAR(13) + CHAR(10)

IF @missing_ix = 1
BEGIN
 SELECT OBJECT_SCHEMA_NAME(d.OBJECT_ID) AS schema_name
  ,OBJECT_NAME(d.OBJECT_ID) AS table_name
  ,'CREATE INDEX ' + CAST(OBJECT_SCHEMA_NAME(d.OBJECT_ID) + '__' + OBJECT_NAME(d.OBJECT_ID) + '__' + REPLACE(REPLACE(COALESCE(REPLACE(d.equality_columns, ', ', '__'), ''), '[', ''), ']', '') + '__' + REPLACE(REPLACE(COALESCE(REPLACE(d.inequality_columns, ', ', '__'), ''), '[', ''), ']', '') + '__' + REPLACE(REPLACE(COALESCE(REPLACE(d.included_columns, ', ', '__'), ''), '[', ''), ']', '') AS SYSNAME) + @crlf + 'ON ' + OBJECT_SCHEMA_NAME(d.OBJECT_ID) + '.' + OBJECT_NAME(d.OBJECT_ID) + ' ' + '(' + COALESCE(d.equality_columns + COALESCE(', ' + d.inequality_columns, ''), d.inequality_columns) + ')' + @crlf + COALESCE('INCLUDE(' + d.included_columns + ')', '') AS ddl
  ,s.user_seeks
  ,s.user_scans
  ,s.avg_user_impact
 FROM sys.dm_db_missing_index_details AS d
 INNER JOIN sys.dm_db_missing_index_groups AS g ON d.index_handle = g.index_handle
 INNER JOIN sys.dm_db_missing_index_group_stats AS s ON g.index_group_handle = s.group_handle
 WHERE OBJECT_NAME(d.OBJECT_ID) LIKE @tblPat
  AND d.database_id = DB_ID()
 ORDER BY avg_user_impact DESC
END
GO

EXEC sp_MS_Marksystemobject sp_indexinfo

Also look at 

Table Information Script 

Database Information Script

Table Information

Also look at 

Index Information Script 

Database Information Script

USE master
GO

IF OBJECT_ID('sp_tableinfo') IS NOT NULL
 DROP PROCEDURE sp_tableinfo
GO

CREATE PROCEDURE sp_tableinfo @tblPat SYSNAME = '%'
 ,@sort CHAR(1) = 'm'
AS
WITH t
AS (
 SELECT SCHEMA_NAME(t.schema_id) AS schema_name
  ,t.NAME AS table_name
  ,SUM(CASE 
    WHEN p.index_id IN (
      0
      ,1
      )
     AND a.type_desc = 'IN_ROW_DATA'
     THEN p.rows
    ELSE 0
    END) AS rows
  ,SUM(CAST((a.total_pages * 8.00) / 1024 AS DECIMAL(9, 2))) AS MB
  ,SUM(a.total_pages) AS pages
  ,ds.NAME AS location
 FROM sys.tables AS t
 INNER JOIN sys.partitions AS p ON t.OBJECT_ID = p.OBJECT_ID
 INNER JOIN sys.allocation_units AS a ON p.hobt_id = a.container_id
 INNER JOIN sys.data_spaces AS ds ON a.data_space_id = ds.data_space_id
 WHERE t.NAME LIKE @tblPat
 GROUP BY SCHEMA_NAME(t.schema_id)
  ,t.NAME
  ,ds.NAME
 )
SELECT schema_name
 ,table_name
 ,rows
 ,MB
 ,pages
 ,location
FROM t
ORDER BY CASE 
  WHEN @sort = 'n'
   THEN table_name
  END
 ,CASE 
  WHEN @sort = 'r'
   THEN rows
  END DESC
 ,CASE 
  WHEN @sort = 'm'
   THEN MB
  END DESC
 ,CASE 
  WHEN @sort = 's'
   THEN schema_name
  END
GO

EXEC sp_MS_Marksystemobject 'sp_tableinfo'

Also look at 

Index Information Script 

Database Information Script