I tried to find a way through various automation activities to build the delimited string, but could not find an answer. Thought perhaps the Loop Dataset activity would do the trick, but got a system error (index out of range) when attempting to use a Set Variable Value activity inside of it.
One solution I found was to call a function in an Execute Script activity that returned the delimited string, and then use that in a second Execute Script activity. I’m working with Oracle, so I created a function in a package that took no parameters and returned a string. It basically used a for loop on a cursor to build the comma separated list of ids, returning the completed string. I then called it via a select (SELECT my_package.my_function FROM DUAL) so that the result ended up in the variable Execute_1_SQL. Second step used the variable, as in SELECT * FROM OTHERTABLE WHERE OTHERTABLE_ID IN (#Execute_1_SQL#);. Looking at the log, I could see the substitution of the delimited list in the statement, and got the expected number of rows back.
Note that you can possibly simplify the first step by using PIVOT/STUFF/whatever in a single SQL statement and bypass going the whole stored procedure route, depending on what sort of database you are using and how current the release is.