I think you're on the right track with a two-step ETL.

Do you have permission to write into a table in the source database?

• If yes, first ETL can copy the id's to the source database, and then select (with join) in the 'main' ETL.

• If no, I might pursue a full copy into a 'staging' table then select with join into the final table.