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  = ?

Not what with what we have. Maybe the scope variable(s) is wrong? — Thom A
– Thom A ♦, Commented Feb 16, 2019 at 11:35
Why Update I Set I.Status = 10 from Invoice I where I.ID = ? instead of UPDATE Invoice SET Status = 10 WHERE ID = ? — Hadi
– Hadi, Commented Feb 16, 2019 at 11:39

Hadi · Accepted Answer · 2019-02-16 12:00:18Z

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]

edited Feb 16, 2019 at 12:00

answered Feb 16, 2019 at 11:47

Hadi

37.5k13 gold badges73 silver badges128 bronze badges

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

6 Comments

Philip Over a year ago

WorkOrderID is of type "String".

Hadi Over a year ago

And InvoiceID of type LONG or Int

Hadi Over a year ago

@Philip can you show the real sql statemet Update I Set I.Status = CASE WHEN C1.Name = 'Cy .. and provide scrennshot of the first form in Execute SQL Task

Philip Over a year ago

InvoiceID is LONG

Philip Over a year ago

ahh geez, thanks for that. Very simple miss in the end.

|

Collectives™ on Stack Overflow

Using Parameters at different points within For Each Loop Container

1 Answer 1

Checking data types and parameter names / order

Workaround

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Checking data types and parameter names / order

Workaround

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related