You may want to look at BIML (Business Intelligence Markup Language). It comes with BIDS Helper.

It's metadata driven and allows you to automate SSIS package generation.

I've implemented this scenario, but moving tables from SQL Server source to staging tables in SQL Server (not Oracle). Source and staging tables should have the same table and column names. Then, columns will be automatically mapped for each table.

This script is an example to get started. It creates SSIS package to export each table in the database to a flatfile. You'll see it is dynamic and you don't have to manually create them.

I've a framework I about to publish that might also give you ideas. I'll let you know when it's published.