June 7, 2011 at 10:34 am
I have a stored proc that returns a resultset in addition to an output param, i.e.,
exec storedproc @parm1, @parm2 output
I need to use the result set in a later transformation and I need the output parameter to populate a package variable (Scope is package).
I was trying to do this in a OLE DB Source task in my Data Flow task using the SQL Command access mode, but it seems that the output parm is never put into the package variable. The result set returns data just fine, it reads the input parm just fine, but the package variable is never populated with the output parm.
Any ideas on how I can implement this? A different source? Or completely different design?
TIA
-A.
June 7, 2011 at 11:59 am
SSIS 2005 or 2008?
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
June 7, 2011 at 12:07 pm
This thread basically explains what I was thinking you would have to do but I would call it a workaround if anything...not really an elegant solution by any means.
http://social.msdn.microsoft.com/Forums/en-US/sqlintegrationservices/thread/d85668b6-2981-4b1c-978d-227483d03bae/[/url]
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
June 8, 2011 at 2:05 pm
This is in 2008.
The link provided above does describe a work around.
Thanks
-A.
August 18, 2011 at 4:21 pm
opc.three (6/7/2011)
This thread basically explains what I was thinking you would have to do but I would call it a workaround if anything...not really an elegant solution by any means.http://social.msdn.microsoft.com/Forums/en-US/sqlintegrationservices/thread/d85668b6-2981-4b1c-978d-227483d03bae/[/url]
Bit of a necro but figured I'd just tag it as a solution to the exact same problem.
This is MORONIC... but thank you for prividing the link to the workaround. What the HELL was MS thinking here?
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
August 18, 2011 at 11:21 pm
Evil Kraig F (8/18/2011)
opc.three (6/7/2011)
This thread basically explains what I was thinking you would have to do but I would call it a workaround if anything...not really an elegant solution by any means.http://social.msdn.microsoft.com/Forums/en-US/sqlintegrationservices/thread/d85668b6-2981-4b1c-978d-227483d03bae/[/url]
Bit of a necro but figured I'd just tag it as a solution to the exact same problem.
This is MORONIC... but thank you for prividing the link to the workaround. What the HELL was MS thinking here?
HTH. I had the initial thought about how to go about this one and hit up Google...Todd confirmed my suspicions and laid it out quite nicely so I figured why re-invent the wheel (or forum post as it were) 😎
I am not sure why this functionality didn't make it into SSIS. I just tested the OleDbCommand & OleDbDataReader directly in .NET and it's more than happy to allow me to call a proc, capture its resultset as well as capture any output parameters as one unit of work. The only thing I can conclude is the SSIS team has had better things to do than write the plumbing to map that particular functionality from .NET into the product.
On a slightly related note, I had a similar reaction to yours when I first learned you cannot have SSIS pull multiple resultsets into multiple Variables of type Object, i.e. RecordSets, using an Execute SQL Task. This is also something trivial to do in .NET directly, it just needs to be mapped into the SSIS environment.
I haven't gone too deep on Denali SSIS to date. Maybe it'll be there...or maybe on the next go around. Sigh. 🙂
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply