Working with Oracle and SSIS/DTS on a 64-bit windows server has been a painful experience.

Here are the steps to connect to Oracle if you’re setting up a new SSIS package.

1. Create a connection using the .net Providers\OracleClient Data Provider
2. Use the DataReader source type.  Select the Oracle connection you created in step one.
3. Use whatever destination you need to – it doesn’t matter at this point.  I typically use the OLE DB for SQL Server destinations.
4. Schedule as a job with the integration services type step.  No 32 bit DTEXEC modification needed as was the case with DTS steps that hit Oracle. 

You may hit some quirky errors with overflows or numeric data types.  It’s good to make sure you have the latest oracle patchset applied before proceeding with workarounds.