SSIS Looping with Return Value from Stored Procedure

Question

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:

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:

And my expressions for the loop are:

What am I missing? Any help is appreciated!

Hadi Hadi · Accepted Answer · 2019-02-06T05:43:39

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

References

SSIS Looping with Return Value from Stored Procedure

1 Answers