WHERE Column IN from parameter in DB2 over SSIS

Question

I want to do the following command in a SSIS Package to DB2.

UPDATE MyTable
    SET Col1 = ?,
        Col2 = ?
WHERE Col3 IN (?)

The Parameters are connected and the package is finished successfully but no row is updated. The Col3 contains values like 123, 452 and so on and the third parameter is a string with a content like 345,432,456,432,667,123,456.

What have I to change to be able to update the rows?

I tried it with the following. In SQL Server it would work but in DB2 not.

UPDATE MyTable
    SET Col1 = ?,
        Col2 = ?
WHERE ? LIKE '%' CONCAT Col3 CONCAT '%'

Edit

The value in the third Parameter is '345','432','456','432','667','123','456'. The content in Col3 is also a string.

Answer 1

You have an ability to "tokenize" the string passed as a parameter.

UPDATE MyTable m
    SET Col1 = ?,
        Col2 = ?
WHERE Col3 IN
(
  select TOKEN
  from xmltable
  (
  'for $id in tokenize($s, ",") return <i>{string($id)}</i>' 
  passing cast (? as varchar(100)) as "s"
  columns 
    TOKEN integer path 'if (. castable as xs:integer) then xs:integer(.) else ()'
  ) t
);

The subselect in the WHERE clause produces a "virtual" table of int values from a string (for example, 345,432,456,432,667,123,456) passed as a 3-rd parameter.

Answer 2

You're putting all the values in a single string and SQL thinks it's one value '345,432,456,432,667,123,456'.

You will have to dynamically produce the list of in statements or if the number of rows to delete are constant, put in a separate variable for each row to delete.

Answer 3

You must use expressions to achieve that. Use SQL Command from variable and select a variable where you define a similar expression:

"UPDATE MyTable
    SET Col1 ='" +(DT_WSTR,50)@[User::Var1] +"' ,
        Col2 ='" +(DT_WSTR,50)@[User::Var2] +"'
WHERE Col3 IN (" + (DT_WSTR,50)@[User::Var3] + ")"

• https://www.mssqltips.com/sqlservertip/4564/ssis-variable-and-expression-example--part-1/
• https://docs.microsoft.com/en-us/sql/integration-services/expressions/integration-services-ssis-expressions?view=sql-server-2017

---

From a while i didn't use SSIS with DB2, so if there is no SQL Command from variable optio. Click on the data flow task, press F4 to show the properties tab, go to expressions. In the expression form you will find [OLEDB Source].SQLCommand property you can write the expression there.