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
– billinkc, Commented May 14, 2021 at 14:16
I don't think you need to specify output in the command, like you would in SSMS — Mark Wojciechowicz
– Mark Wojciechowicz, Commented May 14, 2021 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
– Arpit Chinmay, Commented May 17, 2021 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
– Mark Wojciechowicz, Commented May 17, 2021 at 20:15

Arpit Chinmay · Accepted Answer · 2021-05-20 12:34:48Z

0

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.

answered May 20, 2021 at 12:34

Arpit Chinmay

3426 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related