Execute SQL Task and Stored Procedures

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Joe - 19 Dec 2006 14:43 GMT

Hi,

I am having difficulty in returning a value from a stored procedure
into my SSIS package with the Execute SQL task. The stored procedure
returns a single integer number and I know that the procedure works
because I tested it independent of SSIS.

My SQL statement is the name of the stored procedure and the parameter:

Usp_IDS_Test 0

I am using the OLE DB connection type, and I have the IsStoredProcedure
property set to True and the ResultSet is set to SingleRow.

When I execute the task, it fails with the following error message:
There is an invalid number of result bindings returned for the
ResultSetType: ResultSetType_SingleRow

When I change the result set type to "Full Result Set" I get a
similar error message.

I have looked at the www.sqlis.com website and it does not address this
specific situation.

Can anyone tell me what I am doing wrong?

Reply to this Message

Remo - 19 Dec 2006 19:39 GMT

I am also faced the problem but still no solution for retruning a value.
Instead returning trough RETURN I am genearting the result from the SP by
SELECT <column> and in Result Set page of the Task editor am assigning
<column> to the a variable and it did work.

Try this, you may get a solution.

thanks,
Mathew

> Hi,
>
[quoted text clipped - 21 lines]
>
> Can anyone tell me what I am doing wrong?

Reply to this Message

Todd C - 19 Dec 2006 20:21 GMT

Couple of things that are not always so obvious:

In some cases, the Result set in an Execute SQL Task must be named "0", not
the default of "FirstResultSet" or whatever else it puts there.
Next, instead of creating a Variable and THEN assigning the resultset to the
variable, try creating the variable from within the SQL Task by selecting
"<New Variable>" in the variable name column of the Result set page.

Both have worked for me in fixing errors.

HTH

Signature

Todd Chittenden

> I am also faced the problem but still no solution for retruning a value.
> Instead returning trough RETURN I am genearting the result from the SP by
[quoted text clipped - 31 lines]
> >
> > Can anyone tell me what I am doing wrong?

Reply to this Message