For a little while now I have been trying to get MS SQL Server 2008 R2 on Windows Server 2008 R2 64-bit to connect to an Oracle 10g database but have run into some issues doing so with some other more complicated solutions. My approaches to date have involved extra Oracle client software and working around the mix of 32-bit and 64-bit Microsoft SQL Server software.
To be honest, I just want something that works given that I am pretty much a hack when it comes to databases who has learnt as I have gone along.
As luck would have it, I have found some free connector software – Attunity Connector V1.2 for Oracle in both 32-bit and 64-bit flavours. This connector allows you to connect to an Oracle database within an SSIS package and use it as either a data source or destination plus it is apparently much quicker than other alternative methods (although I won’t be able to substantiate this myself given that the other methods have not worked for me).
The installation also takes some of the guesswork away by installing both the 32-bit and 64-bit Attunity connectors if you install the 64-bit version. The only other stuff you should need are the 32-bit and 64-bit Oracle clients and appropriate tnsnames.ora files for both clients containing the required entries for the Oracle database to which you want to connect.
Anyway, I will let you know how I go in the test environment!