Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Temp tables not working in SSIS packages Expand / Collapse
Author
Message
Posted Wednesday, June 4, 2008 12:35 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 1:35 PM
Points: 1,305, Visits: 777
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
Posted Wednesday, June 4, 2008 1:28 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, August 10, 2010 5:07 AM
Points: 2,661, 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
Posted Wednesday, June 4, 2008 2:44 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 1:35 PM
Points: 1,305, Visits: 777
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
Posted Friday, August 8, 2008 5:06 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

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
Posted Monday, August 11, 2008 3:25 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued 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
Posted Wednesday, January 28, 2009 10:08 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, July 24, 2013 8:30 AM
Points: 11, Visits: 50
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
Posted Thursday, January 29, 2009 8:47 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Sunday, March 16, 2014 10:26 AM
Points: 536, Visits: 516
FMTONLY option will do the trick.
Post #646023
Posted Thursday, January 29, 2009 10:59 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, September 15, 2014 4:46 PM
Points: 1,045, Visits: 2,725
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
Independent Business Intelligence Consultant
www.TimMitchell.net
@Tim_Mitchell

Post #646656
Posted Friday, January 30, 2009 7:01 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Sunday, March 16, 2014 10:26 AM
Points: 536, Visits: 516
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
Posted Friday, January 30, 2009 7:30 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, September 15, 2014 4:46 PM
Points: 1,045, Visits: 2,725
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
Independent Business Intelligence Consultant
www.TimMitchell.net
@Tim_Mitchell

Post #646941
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse