Continuing from my previous post SSIS – Pass a list of values to a data flow source.

Step 3: create a user defined function on DB2 to convert the list of the Status IDs into rows.

Now I need to have a function on DB2 to convert the list of the Status IDs into rows.

Actually I really need 2 user defined functions.

First, I need one to create a table to tell me where each comma starts and ends.

Run this query.

SELECT * FROM TABLE(mySchema.ListToTable_IDX(’1,101,203′)) AS A

Here is the result telling me where each comma starts and ends.

Second, I need a function to return me back the actual values as rows in a table.

Run this query.

SELECT * FROM TABLE(mySchema.ListToTable(’1,101,203′)) AS A

Here is the result telling me what each value is as rows in a table.

Here is an example how you can call the user defined function in DB2.

Now if I need to use this user defined function in a data source of a data flow step from my SSIS package, taking a parameter, I would write the query link this: