Using Temp tables in SSIS

Question

I am using a Temporary table in Stored Procedure in SQL Server. I am trying to use that SP in OLE DB Source Editor.

I can see the data output returned in the Query Builder that comes with Build Query button. But when I Click the Columns tab, I am getting the below error.

- TITLE: Microsoft Visual Studio

Error at Data Flow Task [OLE DB Source [1]]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft SQL Server Native Client 10.0" Hresult: 0x80004005 Description: "Invalid object name '##Payment'.".

Error at Data Flow Task [OLE DB Source [1]]: Unable to retrieve column information from the data source. Make sure your target table in the database is available.

Does that mean I cant use temp tables in SP, if I want it to be consumed by SSIS

Answer 1

UPDATE November 2020.
This post has been superceeded by How to EXEC a stored procedure from SSIS to get its output to text file that describes how to run a stored procedure from SSIS

exec mySproc WITH RESULT SETS ((i int))

look at the solution provided by Troy Witthoeft

Old answer
There is another solution mentioned at https://web.archive.org/web/20120915093807/http://sqlserverpedia.com/blog/sql-server-bloggers/ssis-stored-procedure-metadata. Look at option 3. (November 2020; updated link)

Quote: Add some meta-data and the "set nocount on" to the stored procedure with a "short circuited if clause" (if 1=0), and a bogus select statement at the top. I've tested with trying to leave the "set nocount on" out and it did not work.

CREATE PROCEDURE [dbo] . [GenMetadata] AS 
SET NOCOUNT ON 
IF 1 = 0 
    BEGIN
         -- Publish metadata 
        SELECT   CAST (NULL AS INT ) AS id , 
                CAST (NULL AS NCHAR ( 10 )) AS [Name] , 
                CAST (NULL AS NCHAR ( 10 )) AS SirName 
    END 

 -- Do real work starting here 
CREATE TABLE #test 
    ( 
      [id] [int] NULL, 
      [Name] [nchar] ( 10 ) NULL, 
      [SirName] [nchar] ( 10 ) NULL 
    ) 

Answer 2

I used

SET FMTONLY OFF at the start of procedure, which will tell not to process rows to the client when it is not being executed as there is no temp table while parsing the SP, hence no column available while parsing.

It got me working finally :)