Can I retrieve a result set from a sp into a variable within a Execute SQL Task?

Can I retrieve a result set from a sp into a variable within a Execute SQL Task?I can't see why not... Have you tried?|||I have done it using the result set property for that. I guess you can do the same using a SP but may be you need to use the parameter tab of the Execute SQl task to set an output parameter that is mapped to the variable.|||I have tried this with just about every combination available with no luck. Do you need to add anything to the exec command? I am passing it one variable and it now looks like this "exec sp_name '20071201'" Do I need to change it to look like this? "exec sp_name '20071201', ?" or this? "exec sp_name '20071201', ? OUTPUT". I also have a variable set up under result set and included it in executevariablevalue. Just to be clear the SP is not putting anything in a variable but builds a report in a fiels and at the end does a select @.report to display what was built in @.report. I hope this is clear. Thanks.|||What *exactly* do you have entered in the SQLStatement box?|||I am building it in a variable that contains

exec sp_name '20071201'|||So just to be clear... You don't have "exec sp_name @.User::Var" listed.|||

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

|||I am passing the sql in a variable and have a resultset set up to retreive the output/result set from the SP.|||

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

|||I looked at Jamies example and it is pretty much what I am doing with the exception I am trying to get a result set from a sp not a select statement.

This is the error message I am getting
.

Error: 0xC001F009 at D3OLNAC3: The type of the value being assigned to variable "User::spmessage" differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object.
Error: 0xC002F210 at Run sp_D3ALNAC1, Execute SQL Task: Executing the query "exec sp_D3acls1 '20061204'" failed with the following error: "The type of the value being assigned to variable "User::spmessage" differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object.
". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
Error: 0xC001F009 at D3OLNAC3: The type of the value being assigned to variable "User::spmessage" differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object.
Task failed: Run sp_D3ALNAC1
Error: 0xC0019001 at Run sp_D3ALNAC1: The wrapper was unable to set the value of the variable specified in the ExecutionValueVariable property.
Warning: 0x80019002 at D3OLNAC3: The Execution method succeeded, but the number of errors raised (7) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.
SSIS package "D3OLNAC3.dtsx" finished: Failure.

I have a variable named User::spmessage defined as string. I have this in execvaluevariable on the execute sql task that runs the sp. On this task I also have a result set set up for the same variable with a result set name of 0. I also have result set type properties set up to say ResultSetType_Rowset and have also tried singlerow.

I am passing the sql to the task in a different variable and it contains
exec sp_name '20061201'

I have also tried different combination of this by adding ,? and ,? OUTPUT to the end of that exec statement.

I really appreciate all of your help and guidance.|||TTT|||Is User::spmessage a variable of the "object" data type?