SQL Server to Oracle export using Microsoft OLEDB Provider for Oracle

Question

I am trying to Export Data from SQL Server to Oracle using the "Microsoft OLEDB Provider for Oracle".

I am using "Export Data..." option from Task menu.

I get to connect to the source database (SQL Server) but when I try to connect to the destination database (Oracle) using "Microsoft OLEDB Provider for Oracle" option, I get the following error.

"ORA-06413: Connection not open"

I also checked my connections (tnsnames.ora) and I am able to successfully connect using SQL Developer to my local Oracle XE instance. My OS is Windows 8 running on a x64 machine.

Any guess what could be the problem here?

Answer 1

I would try installing the Oracle Data Access Components (ODAC).

You will be able to download them from this link. http://www.oracle.com/technetwork/database/windows/downloads/index-090165.html

Answer 2

I found an answer to this issue on these forum posts:

http://social.msdn.microsoft.com/Forums/sqlserver/en-US/ab662d63-6385-4f73-b27f-d526048f601f/connecting-to-oracle-on-64bit-x64-machine

and on this:

Link

The problem is with the "(x86)" string in the folder name in which SQL Server DTS is installed. I just copied/pasted the whole "Microsoft SQL Server" folder from "Program Files (X86)" to "C:" and ran the DTSWizard.exe, and it worked. :)