SQL 2008 Enterprise Edtion has a nice feature "change data capture"(CDC) which would reduce a lot of efforts when dealing with SCD2. However, your SQL 2005 may have to stay in production for a long while or you may not have 2008 Enterprise Edition, and you need to extract data from Oracle database. Here is a good news for you.
Attunity provide a simple solution for replicating and integrating Oracle data efficiently and in real-time using SSIS.
The following information is from Attunity:
"Attunity Oracle-CDC for SSIS addresses the key challenges that data integration teams face in addressing business and IT requirements. Using it, you can:
- Simplify and accelerate CDC implementation
- Deliver real-time information
- Design efficient data integration solutions
- Leverage existing SQL Server investments
- Reduce resources and costs
More info can be found in its website http://www.attunity.com/oracle_cdc_for_ssis
Although the tool is not free, I would like to try its trail version whenever I have an opportunity.