Sergiy (3/18/2013)
dtibz01 (3/18/2013)The part that I find to be a little frustrating, is that the data types for the parameters defined for WITH RESULT SETS are defined elsewhere in the SP to begin with..... But I guess I'm just complaining. And probably still don't fully understand what the newer metadata gathering functionality actually buys (big picture). As it relates to me on this day though, it sucks.
Let's take procedure sp_help.
It returns different recordsets depending on if parameter is not supplied, if the name suapplied as parameter is a DB object or if it's a DB type.
Very different resultsets.
Now, how SSIS package suppose to know which of the data sets will be returned on any particular run?
I know, you do not write procedures like that.
But can they over there in Redmond really rely on it while designing a product?
Fair enough Sergiy, and thank you for the reply. The comparison to sp_help seems like one of apples to oranges in this case, or at least Granny Smith to Red Delicious. But I get your point in general about potentially different or varying data sets in the context of SSIS. I don't deal with SSIS much, outside of the infrastructure to support its use so this really my first deeper dive into the plumbing.
Now, how SSIS package suppose to know which of the data sets will be returned on any particular run?
In response to this, the only answer I have off the cuff is I'm not sure. How is it handled on 2008R2? Is it simply not, or overlooked on purpose somehow in that context? Because in that version it doesn't seem to matter, this is however just an assumption based on the fact that the same package in 2008R2 doesn't fail and not much else.
A previous comment from Phil about SSIS column sniffing a given proc, and his general feeling that forcing the definition of result sets as a departure from "sniffing" is an improvement (which makes a whole mess of sense) gives me the impression that maybe previous versions are somehow lacking in this area. Or maybe at least operating with fewer rules. This new metadata method does seem to set one up to enforce a greater level of precision in regard to data in general, as well as an added level of flexibility to play reindeer games with result sets (at least by name) if desired. Assuming I have an accurate general understanding of WITH RESULT SETS....
I know, you do not write procedures like that.
But can they over there in Redmond really rely on it while designing a product?
I appreciate you giving me the benefit of doubt:). But "they over there in Redmond" absolutely cannot rely on this from me. At least they couldn't until last week. Selfishly I would rather not experience busted procs through improvement, but it is what it is. Again, I thank you for your time and willingness to reply.
D