question

ArpitChinmay-2818 avatar image
0 Votes"
ArpitChinmay-2818 asked ZoeHui-MSFT answered

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

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



sql-server-integration-services
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

ZoeHui-MSFT avatar image
0 Votes"
ZoeHui-MSFT answered

Hi @ArpitChinmay-2818.

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


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

JeffreyWilliams-3310 avatar image
0 Votes"
JeffreyWilliams-3310 answered

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
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.