June 4, 2008 at 12:35 pm
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
June 4, 2008 at 1:28 pm
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:
[font="Courier New"]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
*/[/font]
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.
June 4, 2008 at 2:44 pm
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.
August 8, 2008 at 5:06 pm
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
August 11, 2008 at 3:25 pm
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
January 28, 2009 at 10:08 pm
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?
January 29, 2009 at 8:47 am
FMTONLY option will do the trick.
January 29, 2009 at 10:59 pm
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
January 30, 2009 at 7:01 am
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?
January 30, 2009 at 7:30 am
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
January 30, 2009 at 7:34 am
Tim Mitchell (1/30/2009)
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.
Thanks Tim. I wonder what performance implications it could have, it should be querieng the sys catalog at the most.... but i have seen it running more than once when i used it. The profiler trace is helpful in this case.
July 17, 2009 at 3:38 am
Well, I want to remove the confusion that exists in the minds of SSIS developers regarding using temp tables in stored procedure to be used in SSIS package. On so many articles, i have read that using SET FMTONLY option we can use temp tables in sps. YEs thats true, but along with that it is also writeen there that using this approach will cause the sp called multiple times.. That is absolutely wrong. Means, the sp does get called multiple times( 4 times !!) even if u r not using temp tables and the sp is the simple one. The cause for this is that at each stage(validate,preexecute etc) the sp gets called every time and nothign has related to whether the sp has temp tables or not!!!
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply