April 19, 2010 at 9:31 am
Hello All,
I have problem connecting to Oracle server using SSIS. I'm using Microsoft OLE DB Provider for OLE DB Source in my Dataflow. I have the following environment.
Operating System: MS Windows Server 2003 R2 Enterprise X64 Edition. Service Pack 2.
Oracle Client: Oracle 11g Release 1 (11.1) for Microsoft Windows x64
Visual Studio: Microsoft Visual Studio 2008 Version 9.0.30729.1 SP Microsoft .NET Framework Version 3.5 SP1
Installed Edition: IDE Standard
SQL Server 2008
Here is the error message I'm seeing:
Test connection failed because of an error in initializing provider. Oracle client and networking components were not found. These components are supplied by Oracle Corporation and are part of the Oracle Version 7.3.3 or later client software installation.
Provider is unable to function until these components are installed.
I think the issue is with connecting to Oracle on 64-bit machine. Could anyone help me with the soultion to resolve this problem.
Thanks in advance,
-Amith Vemuganti
April 21, 2010 at 8:49 am
Hi,
your right. this is because of a missing oracle client installation on your windows server.
If you need more info about connecting to oracle (e.g tnsnames.ora sqlnet.ora etc) ask your oracle Db administrator.
he knows the name of the Oracel instance and the port to connect to. ususaly 1521
April 22, 2010 at 1:13 pm
Hi,
I have the following Oracle Client installed on my machine. I'm able to connect to the server with SQLPLUS. The problem is only when trying to create an OLE DB connection using Microsoft OLE DB Driver for Oracle in SSIS. I think the issue is related to Oracle connection on 64 bit windows machine from SSIS.
Oracle Client: Oracle 11g Release 1 (11.1) for Microsoft Windows x64
Thanks,
Amith Vemuganti
April 22, 2010 at 5:27 pm
Be sure ORACLE_HOME is properly set by default.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.April 22, 2010 at 5:52 pm
Try using the Allow inprocess for the oracle olebd provider and then create the linked server.
David Weil
October 17, 2010 at 5:09 am
If you are using MS OLEDB provider for Oracle.. it works only in 32-bit runtime environment.. there is no MS provider for oracle connectivity for 64-bit runtime environment.
try Oracle providers or Attunity (recommended by MS) for Oracle connectivity in 64 bit runtime environment.
__________________________________________________________
Yuvaraj
October 26, 2010 at 8:20 am
To work around this you need to install both 32 and 64-bit Oracle clients on your Sql servers.
This link explains what the Oracle DBA must do to install & configure:
We found the instructions by the author, Jeyong Park, to be perfect.
In SSIS "Project Properties" under "Debugging," set option "Run64BitRunTime" to FALSE.
In Sql Agent the job step must use type "Operating System (CmdExec).
The Command needs to execute the 32-bit version of DTExec.exe like this:
"C:\Program Files (x86)\Microsoft SQL Server\90\DTS\Binn\DTExec.exe" /DTS "\MSDB\yourpackagename" /SERVER yourservername /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING V
Hope this works as well for you.
Mike Bourgeois
Kevin Landon
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply