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

It's the Output parameter that causing the hiccup but I'm not awake enough to spot the way to resolve it — billinkc, May 14 '21 at 14:16
I don't think you need to specify output in the command, like you would in SSMS — Mark Wojciechowicz, May 14 '21 at 20:59
@MarkWojciechowicz I removed the OUTPUT parameter from the EXECUTE statement. It now gives me another error. ("Input string not in correct format...") — Arpit Chinmay, May 17 '21 at 12:36
Sorry - I was incorrect, your syntax was right in the first place. I had trouble using the numeric type as the output parameter. I was able to get it to work by using VARCHAR, but my error was `input string was not in the correct format` so that may have been another issue. — Mark Wojciechowicz, May 17 '21 at 20:15

score 0 · Answer 1 · answered May 20 '21 at 12:34

As pointed out by @billinkc, I was able to isolate the problem for me. The issue was being caused by the @cumulativeAverage DECIMAL(5,2) OUTPUT parameter. It was because i was unable to set the precision of the variable in SSIS. Although, i'm still unable to find a way to set the precision. I changed the SP to return FLOAT value for the OUTPUT parameter, configured the SSIS variables to except FLOAT data type and now it works fine.

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

1 Answers1