Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Temp tables not working in SSIS packages


Temp tables not working in SSIS packages

Author
Message
Anders Pedersen
Anders Pedersen
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1430 Visits: 876
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
Michael Earl-395764
Michael Earl-395764
SSCrazy
SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)

Group: General Forum Members
Points: 2705 Visits: 23078
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.
Anders Pedersen
Anders Pedersen
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1430 Visits: 876
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.
Jeff Block
Jeff Block
Grasshopper
Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)

Group: General Forum Members
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
SrikanthSv
SrikanthSv
Valued Member
Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)

Group: General Forum Members
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
Ramachandran Veluthattil
Ramachandran Veluthattil
Grasshopper
Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)

Group: General Forum Members
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?
vishal.gamji
vishal.gamji
Mr or Mrs. 500
Mr or Mrs. 500 (544 reputation)Mr or Mrs. 500 (544 reputation)Mr or Mrs. 500 (544 reputation)Mr or Mrs. 500 (544 reputation)Mr or Mrs. 500 (544 reputation)Mr or Mrs. 500 (544 reputation)Mr or Mrs. 500 (544 reputation)Mr or Mrs. 500 (544 reputation)

Group: General Forum Members
Points: 544 Visits: 532
FMTONLY option will do the trick.
Tim Mitchell
Tim Mitchell
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1122 Visits: 2908
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, Microsoft Data Platform MVP
Data Warehouse and ETL Consultant
TimMitchell.net | @Tim_Mitchell | Tyleris.com
ETL Best Practices


vishal.gamji
vishal.gamji
Mr or Mrs. 500
Mr or Mrs. 500 (544 reputation)Mr or Mrs. 500 (544 reputation)Mr or Mrs. 500 (544 reputation)Mr or Mrs. 500 (544 reputation)Mr or Mrs. 500 (544 reputation)Mr or Mrs. 500 (544 reputation)Mr or Mrs. 500 (544 reputation)Mr or Mrs. 500 (544 reputation)

Group: General Forum Members
Points: 544 Visits: 532
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?
Tim Mitchell
Tim Mitchell
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1122 Visits: 2908
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, Microsoft Data Platform MVP
Data Warehouse and ETL Consultant
TimMitchell.net | @Tim_Mitchell | Tyleris.com
ETL Best Practices


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search