Aha!, so the SSIS variable is to pass the SQL statement tothe Execute SQL task
I thoght you were trying to put the output of the SP into a SSIS variable which is different.
Please clarify this....
agentf1 wrote:
Can I retrieve a result set from a sp into a variable within a Execute SQL Task
There's a downloadable demo of doing this here:
Execute SQL Task into an object variable - Shred it with a Foreach loop(http://blogs.conchango.com/jamiethomson/archive/2005/07/04/SSIS-Nugget_3A00_-Execute-SQL-Task-into-an-object-variable-_2D00_-Shred-it-with-a-Foreach-loop.aspx)
It sounds as though you're not interested in the bit about the Foreach loop but the bit about the Execute SQL Task is exactly what you after.
-Jamie
agentf1 wrote:
I am passing the sql in a variable and have a resultset set up to retreive the output/result set from the SP.
So where exactly is the problem
If you need the sql statment to be dynamic; you may want to use an expression inside of the Execute SQL Task to set the SQLStatementSource property:
http://forums.microsoft.com/MSDN/ShowPost.aspx PostID=662860&SiteID=1
Now the second part would be to get the SP output into a SSIS variable; for that you can follow Jamies sugestion. If the SP returns more than one row you will need a SSIS variable of Object type.
agentf1 wrote:
I am passing the sql in a variable and have a resultset set up to retreive the output/result set from the SP.
You haven't told us why this doesn't work. Do you get an error Does anything happen
-Jamie