Error while trying to execute a stored procedure through “Execute SQL task” in SSIS

Question

I have created a stored procedure. While trying to execute it through SSIS I'm running into problems.
The definition of my stored procedure looks something like this:

CREATE PROCEDURE dbo.[procedurename] 
    @startDate DATETIME, 
    @endDate DATETIME, 
    @cumulativeAverage DECIMAL(5,2) OUTPUT
AS
    /*stored procedure body that return a table with 3 columns and assigns value to output variable*/
    RETURN 
GO

I'm capable of running this is SSMS and it works fine. But when I try to run it in Execute SQL task in SSIS, it doesn't work and I get this error:

Error: 0xC002F210 at EXEC at Execute SQL task name. Execute SQL task: Executing the query EXEC DBO.procedurename ?, ?, ?..." failed with the following error: Multiple OLE DB operations generated error. Check each OLE DB status value...

My SSIS scripts are as below.

Result set : Full result set.
SQL source type : direct input.
SQL statement : EXEC dbo.[procedurename] ?, ?, ? OUTPUT

Parameter mappings : variable name- User::startDate, Direction- Input, Data Type- Date, Parameter 
                     Name- 0, Parameter size- -1
                     variable name- User::endDate, Direction- Input, Data Type- Date, Parameter Name- 
                     1, Parameter size- -1
                     variable name- User::cumulativeAverage, Direction- output, Data Type- numeric, 
                     Parameter Name- 2, Parameter size- -1
Result set : Result name- 0, Variable name- User::ResultSet
Variables : Variable name- CumulativeAverage, Scope- package, Datatype- Decimal, Value- 0
            Variable name- EndDate, scope- Package, Datetype- DateTime, Value- 5/03/2021
            Variable name- StartDate, scope- Package, Datetype- DateTime, Value- 12/28/2020
            Variable name- ResultSet, scope- Package, DateType- Object, Value- System.Object

Answer 1

Try putting SET NOCOUNT ON at the beginning of the procedure:

 CREATE PROCEDURE dbo.[procedurename] 
     @startDate DATETIME, 
     @endDate DATETIME, 
     @cumulativeAverage DECIMAL(5,2) OUTPUT
 AS
     SET NOCOUNT ON;

     /*stored procedure body that return a table with 3 columns and assigns value to output variable*/
     RETURN 
 GO

Answer 2

Hi @Arpit Chinmay .

With the data you provided, I met issue with different error message.

I'll do some test locally and once there is any update, I'll tell you immediately.

You may have a try of JeffreyWilliams-3310's method to see if it is helpful.

You may also refer this, hope it could give you some ideas.

input+output+and+return+values+in+ssis+execute+sql+task

Regards，

Zoe

---

If the answer is helpful, please click "Accept Answer" and upvote it.

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.
Hot issues October