1

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!

Hadi
  • 36,233
  • 13
  • 65
  • 124
Dizzy49
  • 1,360
  • 24
  • 35

1 Answers1

1

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

References

Hadi
  • 36,233
  • 13
  • 65
  • 124
  • I need the LoadOrder in the loop as it loads the table each pass. The first pass I need to feed it an OrderID = 1, and then OrderID will equal the output of it from then on. I think I just need to do #3 to fix my loop. – Dizzy49 Feb 06 '19 at 17:43
  • @Dizzy49 i will be waiting for your reply – Hadi Feb 06 '19 at 18:54