Using Temp tables in SSIS

sql-server-2008ssisvisual-studio-2008

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

Best Answer

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 
    )

Related Solutions

Sql – SSIS Package not wanting to fetch metadata of temporary table

Using WITH RESULT SETS to explicitly define the metadata will allow SSIS to skip the sp_describe_first_result_set step and use the metadata that you define. The upside is that you can use this to get SSIS to execute SQL that contains a temporary table (for me, that performance helped a lot); the downside is, you have to manually maintain and update this if anything changes.

Query sample (stored procedure:)

    EXEC ('dbo.MyStoredProcedure')
    WITH RESULT SETS
      (
        (
            MyIntegerColumn INT NOT NULL,
            MyTextColumn VARCHAR(50) NULL,
            MyOtherColumn BIT NULL
        )
      )

Query sample (simple SQL:)

EXEC ('
    CREATE TABLE #a 
      (
        MyIntegerColumn INT NOT NULL,
        MyTextColumn VARCHAR(50) NULL,
        MyOtherColumn BIT NULL
      ) 
    INSERT INTO #a 
      (
        MyIntegerColumn,
        MyTextColumn,
        MyOtherColumn
      )
    SELECT 
        1 AS MyIntegerColumn,
        ''x'' AS MyTextColumn,
        0 AS MyOtherColumn

    SELECT MyIntegerColumn, MyTextColumn, MyOtherColumn
    FROM #a')

WITH RESULT SETS
    (
        (
            MyIntegerColumn INT NOT NULL
           ,MyTextColumn VARCHAR(50) NULL
           ,MyOtherColumn BIT NULL
        )
    )

Sql-server – DTS_E_OLEDBERROR. Error code: 0x80004005.Difference between SQl Native client and oledb provider for sql server

I ran into this issue after releasing a new SSIS project to the production environment.

At first, I was able to validate the package with success, but when I tried to execute the package, I got the DTS_E_OLEDBERROR. Error code: 0x80004005. message.

I tried to validate the package with the servername and password variables set to sensitive, but in this case the validation failed as well.

After setting the environment variables back to non-sensitive again and inserting the environment variable values, I was able to validate and execute the package.

To me it seems like in some cases the metadata in the SSISDB gets corrupted, but unfortunately I've not been able to point down the source of this issue.

I hope this will help someone. I've went through a lot of debug scenarios before I ended up with this (rather unsatisfying) solution.

– TITLE: Microsoft Visual Studio

Best Answer

Related Solutions

Sql – SSIS Package not wanting to fetch metadata of temporary table

Sql-server – DTS_E_OLEDBERROR. Error code: 0x80004005.Difference between SQl Native client and oledb provider for sql server

Related Topic