Using Temp Tables in SSIS

Question

I am using a temporary table in a stored procedure in SQL Server. I am trying to use this SP in the OLE DB source code editor.

I can see the data output returned by Query Builder that comes with the Build Query button. But when I click on the "Columns" tab, I get the following error.

- TITLE: Microsoft Visual Studio

Error in 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 in data flow task [OLE DB source [1]]: it is not possible to get a column of information from a data source. Make sure your target table is in an accessible database.

Does this mean that I cannot use temporary tables in SP if I want SSIS to use it

Answer 1

I used

Set FMTONLY OFF at the beginning of the procedure, which will tell the client not to process the rows when it is not running, since there is no temporary table during SP parsing, so there is no column during parsing.

It helped me at last :)

Answer 2

There is another solution mentioned at http://sqlserverpedia.com/blog/sql-server-bloggers/ssis-stored-procedure-metadata/ . Look at option 3.

Quote: Add some metadata and “set nocount on” to the stored procedure using the if clause (if 1 = 0) and the dummy select statement at the top. I tested trying to leave "set nocount on" and it didn't 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 3

If an error occurs when you are in BIDS, then the ajdams solution will not work, since it only applies to errors that occur when starting a package from SQL Server Agent.

The main problem is that SSIS is trying to resolve metadata. From the point of view of the table ## do not exist, because they can not return metadata for the object during the pre-execution phase. Therefore, you must find a way to satisfy your requirement that the table already exists. There are several solutions:

• Do not use temporary tables. Instead, create a working database and put all your objects in it. Obviously, this probably will not work if you are trying to get data on a server where you are not a dbo, as a production server, so you cannot rely on this solution.

• Use CTE instead of temporary tables. This works if your source server is 2005/2008. This will not help if the source server is 2000.

• Create table ## in a separate Execute SQL command. Set the RetainSameConnection property of the connection to True. Set the DelayValidation parameter to true for the data stream. When you tune the data stream, fake it by temporarily adding the SELECT TOP 0 = CAST (NULL AS INT) field to the beginning of the stored procedure, which has identical metadata for your final output. Remember to remove this from the stored procedure before running the package. It is also a convenient way to exchange temporary tabular data between data streams. If you want the rest of the package to use separate connections so that they can work in parallel, you need to create an additional non-shared connection. This avoids the problem because a temporary table already exists during data flow tasks.

Option 3 achieves your goal, but it is complicated and has the limitation that you need to separate the create ## command in another call to the stored procedure. If you have the ability to create stored procedures on the source server, you probably also have the ability to create other objects, such as staging tables, and this is usually the best solution. These are also side steps associated with TempDB problems, which are also a desirable benefit.

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

Answer 4

No, this is a permission issue. This should help you:

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

Answer 5

For all the hassle, I think it is probably just not worth it. Create a real table in db and trim it before / after loading. If it is for a data warehouse, it does not matter if you have an extra table or two. This gives you SSIS development tools and means you don’t have to worry about temp price tables.

If you want to keep something separate, then simply create your temporary SSIS 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:

• Record the final result in a table.
• Script this table as CREATE in a new window for the new query editor.
• Remove everything except the open and closed parentheses that define the columns.
• Wrap this in another pair of brackets.

• Copy your SP call from

  exec p_MySPWithTempTables?,?

in

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

Answer 7

Instead of table variables, table variables can be used. he will work