Using temporary tables in ssis package

4
votes

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.

2
visual-studio-2010stored-proceduresssissql-server-2012
Are you having problem getting the meta data? If so you need to set SET FMTONLY ON in design to get the meta data and then remove after design to get the data. Here is information to use FMTONLY to get meta data – Tak

2 Answers

8
votes

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/

4
votes

Instead of temp table you can use table variable or cte... these don't have the issue like temp table.

CREATE PROCEDURE some_procedure      
AS
SET NOCOUNT ON

Declare @some_table TABLE (number int)
INSERT INTO @some_table VALUES (250)
SELECT number FROM @some_table