November 24, 2014 at 11:55 pm
Comments posted to this topic are about the item Using SQL Server Stored Procedures in SSIS Data Flow Sources
November 25, 2014 at 1:33 am
Why not simply use EXECUTE WITH RESULT SETS?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
November 25, 2014 at 6:38 am
We've run into some goofy metadata issues even with 2008. To work around them, we add the following as the first query within all of our stored procedures:
[font="Courier New"]
IF (1=0)
BEGIN
SELECT CAST(0 AS INT) AS Column1Name,
CAST(0 AS INT) AS Column2Name,
....
END;
[/font]
We set up this query to match the result set. Just be careful to keep the two in sync, or you could end up with some unexpected results. 🙂 There's probably a better way to do this - but this has been working for us so far.
November 25, 2014 at 6:44 am
tmac3217 (11/25/2014)
We've run into some goofy metadata issues even with 2008. To work around them, we add the following as the first query within all of our stored procedures:[font="Courier New"]
IF (1=0)
BEGIN
SELECT CAST(0 AS INT) AS Column1Name,
CAST(0 AS INT) AS Column2Name,
....
END;
[/font]
We set up this query to match the result set. Just be careful to keep the two in sync, or you could end up with some unexpected results. 🙂 There's probably a better way to do this - but this has been working for us so far.
This method stops working in 2012 onwards. You'll need to use the WITH RESULT SETS method.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
November 25, 2014 at 7:38 am
Good to know - Thanks!!!! We're looking to upgrade this next year. Just one more thing we'll need to tweak as part of our upgrade process/project.
November 25, 2014 at 10:14 am
Koen Verbeeck (11/25/2014)
Why not simply use EXECUTE WITH RESULT SETS?
Oh, that's because many of us still have to use procedures executing in versions previous to MSSQL2012.
Thank you for reading the article, this is the first time I've wrote something and I appreciate your comments.
Adán Bucio.
November 25, 2014 at 11:35 am
in SSIS 2008 we use FMTONLY in the ADO.Net Source task's SQL command text:
SET FMTONLY OFF;
EXEC SomeProcedureNameHere;
does this method still work in SSIS 2012/2014? Microsoft says it will be discontinued in a future version but doesn't really say when: http://msdn.microsoft.com/en-us/library/ms173839.aspx
There are situations where sys.dm_exec_describe_first_result_set doesn't work where FMTONLY does so I hope SSIS isn't trying to use that internally.
November 25, 2014 at 11:46 am
Chris Harshman (11/25/2014)
in SSIS 2008 we use FMTONLY in the ADO.Net Source task's SQL command text:
SET FMTONLY OFF;EXEC SomeProcedureNameHere;
does this method still work in SSIS 2012/2014? Microsoft says it will be discontinued in a future version but doesn't really say when: http://msdn.microsoft.com/en-us/library/ms173839.aspx
There are situations where sys.dm_exec_describe_first_result_set doesn't work where FMTONLY does so I hope SSIS isn't trying to use that internally.
From memory (it was a while ago that I went through all of this) this does not work.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
November 25, 2014 at 2:19 pm
beakdan (11/25/2014)
Koen Verbeeck (11/25/2014)
Why not simply use EXECUTE WITH RESULT SETS?Oh, that's because many of us still have to use procedures executing in versions previous to MSSQL2012.
Thank you for reading the article, this is the first time I've wrote something and I appreciate your comments.
Adán Bucio.
It's a good article.
I went over it again and I noticed you did mention WITH RESULT SETS. I apparently missed it at my first read.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
November 25, 2014 at 3:06 pm
I didn't test SET FMTONLY OFF nor I considered for this article because although It might work for some cases, those cases will waste resources. I used the profiler to see what the connection managers were doing behind the scenes, and for the OLEDB connection this would be the steps executed during the validation for a procedure:
--1
DECLARE@p1 INT;
SET@p1 = 0;
EXECUTE sp_prepare @p1 OUTPUT,
NULL,
--> This would be the command to execute from SSIS
N'SET FMTONLY OFF; EXECUTE dbo.procedure;',
1;
--2
SET NO_BROWSETABLE ON
--3: Since the prepared command has FMTONLY in OFF the next sentence has no effect,
--and the execution will return data
SET FMTONLY ON
EXECUTE sp_execute @p1
SET FMTONLY OFF
--4
SET NO_BROWSETABLE OFF
--5
EXECUTE sp_unprepare @p1
For the procedures where this works, they will return data for each validation call.
And we all know that SSIS validates the metadata at various times during execution:
1.- When the package starts if the property DelayValidation of the DataFlow is set to False (default)
2.- During the PreExecute step of the DataFlow if the property ValidateExternalMetadata is set to True(default)
3.- And I don't know why, the OLEDB source does another validation right before the real execution that the ADO.NET source does not.
So unless you are changing the above mentioned properties from its default values, you do a real execution of your procedure by using this setting. If the procedure returns a big resultset... well you can imagine the effects.
Adán Bucio.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply