bmsqldev bmsqldev - 1 year ago 162
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

select EmpID from Employee

select PersonID from person

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

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
, 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 Source

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.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download