Search code examples
sqlsql-serverssisssis-2012control-flow

how many result test we can give a for execute sql task

I have an ssis package which have a exectue sql task it. I tried to modify the task by adding a stored procedure as sql statement. The stored procedure returns two result sets.

stored procedure:

create proc test
as
begin

select EmpID from Employee

select PersonID from person
end

I mapped the result sets to result set tab in the task with parmeter name 0 & 1 and assigned them to two variables.

enter image description here

When I run the package I got below error

[Execute SQL Task] Error: There is an invalid number of result bindings returned for the ResultSetType: "ResultSetType_Rowset".

However if i delete one result set and execute package , it runs successfully. Is it possible to add more than one result set to execute sql task? What I am missing here?

Thank you

Solution

  • The Execute SQL task cannot access multiple result sets returned by a stored procedure. It can only access the first result set.

    The Result Set tab you are showing in your screen shot is not for assigning different result sets to object-type variables. It is for assigning different columns to scalar variables.

    If you changed your stored proc to return a single result set with two columns, then the way you have it in your screenshot would work.