You’ll want to setup an SSIS variable with the scope of Package. Then add in a SQL Query task to return the statement that you want. Then using the record set which is returned drop than into the SSIS variable. Then use the SSIS variable to in the select statement as part of the data pump task which copies the records from the Oracle database to the SQL Server.
This blog post shows some basics of how to map data back to an SSIS variable. I don’t have any other URLs handy with more specific info on what you are trying to do. If my blog post doesn’t provide enough inside, just let me know and I’ll dig up some additional info for you.