Using Parameters at different points within For Each Loop Container

Question

I have the following steps within the Control Flow, where WorkOrderID is used by the first Execute SQL task and InvoiceID used by the second script task.

Can anyone spot what I'm doing wrong below as it is failing on the Update Invoice Status object?

[Execute SQL Task] Error: Executing the query "Update I Set I.Status = CASE WHEN C1.Name = 'Cy..." failed with the following error: "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

and the query for the Update Invoice Status object is:

Update
    I
 Set
    I.Status = 10
from
    Invoice I 
where 
    I.ID  = ?

Answer 1

Checking data types and parameter names / order

I think that the error is caused by the Variable data type. Check that User::WorkerID is of type VARCHAR while it must be integer. Check that the data type of variable are relevant to the column.

Based on the following article Resolving an SSIS Error Due to Parameters Not Mapped Correctly:

Other reasons this particular error could come up is if your Data Type is set incorrectly, or if the number of query parameters don’t match the variables mapped (and in the correct order).

Also why InvoiceID parameter name is 1 instead of 0? Try change it back to 0

---

Workaround

You can use Expression to build SQL Statement if you still have problems with parameters:

Go to Expression in the Execute SQL Task editor. Select the SQLStatmentSource property and write a similar expression:

"Update
    I
Set
    I.Status = 10
from
    Invoice I 
where 
    I.ID  = " + (DT_WSTR,50)@[User::InvoiceID]