bmsqldev bmsqldev - 1 month ago 8
SQL Question

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

Answer

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.

Comments