This is what i did until now:
Data Flow Task (DFT1) : Used Ole Db Source to extract all the Ids and Save them in a Recordset destination. Used variableZ as Object here
Link the DFT1 to a Foreach Loop Container. "Foreach Ado Enumeration"
Enumeration mode: ROws in the first table.
Variable mapping : VariableY= 0 ( It's defined as string)
Inside the foreach loop i have another DFT2 and Ole DB Source data Access mode = SQl command from variable. I built the expression to grab the SQL QUery + The ID coming from the FOreach loop , one by one.
The problem is that the last output result is exporting only 1 record.
Note that i can have more than 10000 Ids to pass in the query.
I can't pass all of then in the query at once, since in oracle i can put only 999 Ids.
And the ids should be in quotes like
WHERE ID IN ( 'ID1','ID2','ID3','IDnth')