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 :)

Answer 3

If the error was raised while you are in BIDS, then ajdams solution will not work as it only applies to errors raised while running the package from the SQL Server Agent.

The primary problem is that SSIS is struggling to resolve the meta data. From its stand-point, the ## tables don't exist since it can't return the meta data for the object during the pre-execution phase. So you have to find a way to satisfy its requirement that the table already exists. There are a few solutions:

1. Don't use temporary tables. Instead, create a working database and put all your objects in it. Obviously, this probably won't work if you are trying to get the data on a server where you aren't a dbo like a production server, so you can't rely on this solution.

2. Use CTE's instead of temporary tables. This works if your source server is 2005/2008. This won't help if the source server is 2000.

3. Create the ## table in a separate Execute SQL command. Set the connection's RetainSameConnection property to True. Set DelayValidation to true for the data flow. When you setup the data flow, fake it out by temporarily adding a SELECT TOP 0 field = CAST(NULL AS INT) to the top of the stored procedure that has identical meta data to your final output. Remember to remove this from the stored procedure before you run the package. This is also a handy trick for sharing temporary table data between data flows. If you want the rest of the package to use separate connections so that they can run in parallel, then you have to create an additional non-shared connection. This evades the problem since the temporary table already exists at the time the data flow tasks runs.

Option 3 achieves your goal, but it is complicated and has the limitation that you have to separate the create ## command into another stored procedure call. If you have the ability to create stored procedures on the source server, then you probably also have the ability to create other objects like staging tables and this is usually a better solution. It also side-steps possible TempDB contention issues which is a desirable benefit as well.

Good luck and let me know if you need further guidance on how to implement step 3.

Answer 4

Nope, it's a permissions issue. This should help you:

http://support.microsoft.com/kb/933835

Answer 5

For all the hassle involved, I think it is probably just not worth it. Create a real table in the db and truncate it before / after your load. If it's for a datawarehouse it isn't going to matter if you have an extra table or two. This gives you the design-time SSIS tools and means you don't have to worry about the intracacies of temp tables.

If you want to keep things separate, then just create your SSIS temp tables in a separate schema. You can use permissions to make this schmema invisible to all other users.

CREATE SCHEMA [ssis_temp]

CREATE TABLE [ssis_temp].[tempTableName]

Answer 6

These steps helped me:

1. Write the final result set into a table.
2. Script that table as CREATE into a new New Query Editor Window.
3. Remove everything except the open and close brackets that define the columns.
4. Wrap that into another pair of brackets.

5. Recompose the calling of your SP from

   exec p_MySPWithTempTables ?, ?

into

exec p_MySPWithTempTables ?, ? with result sets
(
    (
        ColumnA int,
        ColumnB varchar(10),
        ColumnC datetime
    )
)

Answer 7

You can use table variables instead of temporary tables . it will work