Skip to content
Advertisement

SSIS Looping with Return Value from Stored Procedure

I am trying to create a SSIS Package that loops based on the return value of a stored procedure run in the loop. I keep getting a super NOT helpful error of:

“Error: 0xC002F210 at Load Order, Execute SQL Task: Executing the query “EXEC ? = [Load_Focus_OrderNum] ?, 1” failed with the following error:
“Value does not fall within the expected range.”.
Possible failure reasons:
Problems with the query, “ResultSet” property not set correctly, parameters not set correctly, or connection not established correctly.
Task failed: Load Order”

Here is my setup:

enter image description here

The Load Order stored procedure loads a table with 500 orders at a time, then the last order number is returned (I have confirmed it returns correctly).

DECLARE @spOut int
EXEC @spOut = Load_Focus_OrderNum 1, 1
PRINT @spOut

Returns 638 as expected

I then want it to process the next 500 starting at the next order.

I’m calling my stored procedure with:

EXEC ? = sp_LoadOrders ?, 1

Procedure snippet:

ALTER PROCEDURE [dbo].[LoadOrders] 
    (@PK_ID INT, @OrdType INT)
AS
     -- Loads OrderNumTbl table
    RETURN (SELECT TOP 1 ID FROM OrderNumTbl ORDER BY ID DESC)
GO

My parameter mapping for it is:

enter image description here

And my expressions for the loop are:

enter image description here

What am I missing? Any help is appreciated!

Advertisement

Answer

In the parameter Mapping section, replace the parameter name value with the parameter index >> replace @OrderID with 0 and @T1_ID with 1

References

User contributions licensed under: CC BY-SA
5 People found this is helpful
Advertisement