Temp tables not working in SSIS packages

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » SQL Server 2005 Integration Services » Temp tables not working in SSIS packages

12 posts, Page 1 of 2

1 2 »»

Temp tables not working in SSIS packages

Rate Topic

Display Mode

Topic Options

Author

Message

Anders Pedersen

Posted Wednesday, June 04, 2008 12:35 PM

Ten Centuries

Group: General Forum Members
Last Login: 2 days ago @ 7:35 AM
Points: 1,198, Visits: 639

I for various reasons have some queries that we are exporting to Excel using SSIS. Some of these have multiple queries selecting into temp tables for pre-processing prior to the final select, but they all only have one select statement that returns data. In SSIS I keep getting told that "This SQL Statement is not a query". Below is a sample of when this happens.

select * into #t from TestTable
select * into #t2 from TestTable2

select * from #t2

Now that is of course simplifed to the extreme to prove the point.

I thought that maybe set nocount on would help since at that point it should not return anything to the client except for the last select statement, even tried to make it a stored procedure instead of direct code to see if that would help. No dice.

Some of these things I have been able to rewrite to use a CTE, just wondering if there is something obvious I am missing here.

Thanks

Post #511676

Michael Earl-395764

Posted Wednesday, June 04, 2008 1:28 PM

SSCrazy

Group: General Forum Members
Last Login: Tuesday, August 10, 2010 5:07 AM
Points: 2,732, Visits: 23,078

SSIS, SSRS, and many other tools are going to have some trouble with these. The problem is that SSIS does not actually execute the procedure when it is trying to get the meta-data, so the temp table does not actually exist to return the column information. This happens a lot with stored procedures.

I typically handle this in one of two ways. The first is to use real tables rather than temp tables. This can cause some issues, but depending on the process you are trying to manage can sometimes be of benefit.

The second option is to use a stored procedure and temp tables, but during development create a procedure stub that does nothing but select the column definitions. This usually looks like:

CREATE PROCEDURE MySSISProc
@MyDate DATETIME
AS
SELECT CONVERT(INT,NULL) AS SalesOrderID
, CONVERT(VARCHAR(20),NULL) AS CompanyName
, CONVERT(DATETIME,NULL) AS OrderDate
/*
All other logic commented out
*/

This will allow you to work with the meta-data. Then after deploying the package you can go put the actual procedure logic in. This gets risky because if your procedure logic has a little different meta-data, the SSIS package will error.

Post #511729

Anders Pedersen

Posted Wednesday, June 04, 2008 2:44 PM

Ten Centuries

Group: General Forum Members
Last Login: 2 days ago @ 7:35 AM
Points: 1,198, Visits: 639

Yeah we considered that last approach, but sounds kind of risky (due to the nature of the data).

Would it work to make a user defined function that would do all the weird work in the backend, then just a simple select Col1, Col2, ColEtc from dbo.fcnWhatever?

The good thing is that it has forced my developers to learn CTE, so something good has come out of it. Hopefully they can get a better parser for SSIS with SQL 2011.... (or maybe even 2008!)

Thanks for the feedback, just wanted to make sure I was not missing something obvious.

Post #511784

Jeff Block

Posted Friday, August 08, 2008 5:06 PM

Grasshopper

Group: General Forum Members
Last Login: Tuesday, February 19, 2013 5:52 PM
Points: 20, Visits: 141

you can get around this by setting FMTONLY on if you are executing the stored proc as sql (not ADO command). the ssis execute sql should look like:

set fmtonly on
exec sp

Post #549651

SrikanthSv

Posted Monday, August 11, 2008 3:25 PM

Valued Member

Group: General Forum Members
Last Login: Thursday, June 25, 2009 11:35 AM
Points: 61, Visits: 112

Temp tables can be used in ssis 2005
but for this feature to work you shud set the RetainSameConnection property to true in the Oldedb/Sql Connection.
Once you set this the connection will remain same and can be recognized by the server

Note: Temporary tables exist only for the connection. Global temporary tables can be accessed over different connections

Regards
Venkat
http://sqlblogging.blogspot.com

Post #550635

Ramachandran Veluthattil

Posted Wednesday, January 28, 2009 10:08 PM

Grasshopper

Group: General Forum Members
Last Login: Wednesday, April 10, 2013 4:43 AM
Points: 11, Visits: 47

Nope this solution does not work with 2005. I tried it exactly as said and then got the meta data information then opened up the comments. But when deploying it the same error come VS_ISBROKEN. I tried it using ADO.NET would that be a problem?

Post #645586

vishal.gamji

Posted Thursday, January 29, 2009 8:47 AM

Mr or Mrs. 500

Group: General Forum Members
Last Login: 2 days ago @ 1:20 PM
Points: 536, Visits: 476

FMTONLY option will do the trick.

Post #646023

Tim Mitchell

Posted Thursday, January 29, 2009 10:59 PM

Ten Centuries

Group: General Forum Members
Last Login: Wednesday, May 08, 2013 4:11 PM
Points: 1,033, Visits: 2,593

You should know that the FMTONLY option can cause a performance hit in the designer. If your stored procedure is a long-running operation, your package may appear to hang very frequently while working in the BIDS designer.

hth,
Tim

Tim Mitchell
SQL Server MVP
www.TimMitchell.net
twitter.com/Tim_Mitchell

Post #646656

vishal.gamji

Posted Friday, January 30, 2009 7:01 AM

Mr or Mrs. 500

Group: General Forum Members
Last Login: 2 days ago @ 1:20 PM
Points: 536, Visits: 476

Tim, i do agree that it poses a performance issue - but only at design time ... right? or does it (FMTONLY) also have an impact at run time?

Post #646903

Tim Mitchell

Posted Friday, January 30, 2009 7:30 AM

Ten Centuries

Group: General Forum Members
Last Login: Wednesday, May 08, 2013 4:11 PM
Points: 1,033, Visits: 2,593

My understanding is that there are possible performance implications with runtime as well as design time. I haven't done any runtime comparisons, so I'm speaking purely academically on this, but I have read somewhere that a SP created using FMTONLY could be called multiple times as the package runs. Again, I haven't tested that myself, but it stands to reason that the proc could be run once for validation and then again to actually perform its intended task.

Tim Mitchell
SQL Server MVP
www.TimMitchell.net
twitter.com/Tim_Mitchell

Post #646941

« Prev Topic | Next Topic »

12 posts, Page 1 of 2

1 2 »»

Permissions