I am trying to update a table in SSIS using OLEDB Command.
The objective is to update [Value] according to condition matched from ID_1 and ID_2.
The [Value] column is to be updated from different Columns (hence the '?') using Column Mappings of the SSIS OLEDB Command.
UPDATE MyTable
SET [Value] = CASE
WHEN ID_1 = 'A' AND ID_2 = '1' THEN ?
WHEN ID_1 = 'B' AND ID_2 = '2' THEN ?
ELSE [Value]
END
It should look something like this:
| ID_1 | ID_2 | Value |
|---|---|---|
| A | 1 | 1234 |
| B | 2 | 5678 |
However, it throws an error "The parameter type cannot be deduced because a single expression contains two untyped parameters, '@P1' and '@P2'.
I have tried the solutions here: Multiple parameters in a query using a OLE DB COMMAND in SSIS and Update multiple rows with multiple 'where' clauses for each individual row
I have tried declaring the parameters which throws an error "The variable name '@P1' has already been declared..." and "The undeclared parameter '@P1' is used more than once in the batch being analyzed."
DECLARE @P1 AS DECIMAL(15,4) = ?
DECLATE @P2 AS DECIMAL(15,4) = ?
UPDATE MyTable
SET [Value] = CASE
WHEN ID_1 = 'A' AND ID_2 = '1' THEN @P1
WHEN ID_1 = 'B' AND ID_2 = '2' THEN @P2
ELSE [Value]
END
However, none of the workarounds have worked for me. Is there any other way to achieve this result? Can someone please help me out here? Thanks a lot in advance.
WHEN ID_1 = 'A' AND ID_2 = '1' THEN CAST(? as DECIMAL(15,4), etc.