Using temporary tables in ssis package

Question

i have a problem - I want to use temp table in stored procedure is SQL Server, which will be executed from SSIS package. I read some tips how to do it and I tried this one (first answer): Using Temp tables in SSIS but it didn't work. I have MS Visual Studio 2010, couldn't be problem with this version? Here is my code in stored proc.:

CREATE PROCEDURE some_procedure      
AS
SET NOCOUNT ON
IF 1 = 0
BEGIN
  SELECT CAST(NULL AS int) as number
END
CREATE TABLE #some_table (number int)
INSERT INTO #some_table VALUES (250)
SELECT number FROM #some_table

Thanks for any advice or experience.

Here is error message from 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 11.0" Hresult: 0x80004005 Description: "The metadata could not be determined because statement 'INSERT INTO #some_table VALUES (250)' in procedure 'some_procedure' uses a temp table.".

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.

Answer 1

In SQL Server 2012 if you use temporary tables you must specify a results set.

This is an issue with the sp_describe_first_result_set procedure that SSIS uses to returns the output metadata.

E.g.

EXEC dbo.RptResults_StoredProcedure

Becomes

EXEC dbo.RptResults_StoredProcedure
WITH RESULT SETS
((
    Date NVARCHAR(10),
    Location VARCHAR(12),
    Department CHAR(1),
    Shift CHAR(1),
    ForecastSales DECIMAL(18,2),
    ActualSales DECIMAL(18,2)
))

For more information view

http://blog.concentra.co.uk/2014/08/22/column-metadata-determined-correctly-ssis-data-flow-task-stored-procedure-inputs/