Using SQL Server Stored Procedures in SSIS Data Flow Sources

Question

Using SQL Server Stored Procedures in SSIS Data Flow Sources

beakdan

Old Hand

Points: 393
More actions
November 24, 2014 at 11:55 pm

#301441

Comments posted to this topic are about the item Using SQL Server Stored Procedures in SSIS Data Flow Sources

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Koen Verbeeck SSC Guru Points: 259115 More actions · Answer 1

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

tmac3217 SSC Veteran Points: 277 More actions · Answer 2

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.

Phil Parkin SSC Guru Points: 246979 More actions · Answer 3

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.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

tmac3217 SSC Veteran Points: 277 More actions · Answer 4

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.

beakdan Old Hand Points: 393 More actions · Answer 5

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.

Chris Harshman SSC-Forever Points: 42192 More actions · Answer 6

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.

Phil Parkin SSC Guru Points: 246979 More actions · Answer 7

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.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Koen Verbeeck SSC Guru Points: 259115 More actions · Answer 8

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

beakdan Old Hand Points: 393 More actions · Answer 9

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.