June 27, 2013 at 4:38 pm
We are running SQL Server 2012 EE 64-Bit Eval Copy on Windows Server 2008 R2 EE 64-Bit SP1 on our DB Server. We have installed Oracle 11G Client Install. We did a Custom Install and selected the following:
SQL*Plus
Oracle Net
Oracle ODBC Driver
Oracle Services For Microsoft Transaction Server
Oracle Objects for OLE
Oracle Provider for OLE DB
Oracle Data Provider for .NET
Configured sqlnet.ora and tnsnames.ora files.
Tested tnsping to our Oracle Server/Database and it worked.
Tested sqlplus to our Oracle Server/Database and it worked.
Tested ODBC (System DSN and 'Oracle in OraClient11g_home1' driver) to our Oracle Server/Database and it worked.
Set up Linked SQL Server to Oracle using the following and it worked:
--With Microsoft OLE DB Provider for Oracle, use the Oracle server alias
--(that is configured in the TNSNames.Ora file) for the @datasrc parameter.
------------------------------------------------------------------------
-- Test_DBTest Link
------------------------------------------------------------------------
-- Add a linked server for Test DBTest.
USE [master]
GO
EXEC sp_addlinkedserver
@server= 'Test_DBTest_shared'
,@srvproduct = 'DBTest'
,@provider= 'OraOLEDB.Oracle'
,@datasrc= 'DBTest'
GO
-- Add a login for the the remote linked server.
USE [master]
GO
EXEC sp_addlinkedsrvlogin
@rmtsrvname= 'Test_DBTest_shared'
,@useself= 'False'
,@locallogin= NULL
,@rmtuser= 'shared'
,@rmtpassword= 'Test1232'
Then to create the SSIS Package, I go into Microsoft Visual Studio 2012 (it opens and displays Microsoft Visual Studio 2010 Shell). When I try to create a new OLE DB Connection to Oracle via Connection Manager, I select the "Native OLE DB\Microsoft OLE DB Provider to Oracle" I get the following error:
"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."
The Connection Manager does not display any other Oracle Providers. I saw the following during my research:
"Problems
Because BIDS is a 32 bit application, as well on 32 as on 64 bit machines, it cannot see the 64 bit driver for Oracle. Because of this, connecting to Oracle from BIDS on a 64 bit machine will never work when you install the 64 bit Oracle client."
Is BIDS and Visual Studio the same application or uses the same code? Since, I installed SQL Server 2012 EE 64-Bit Eval Copy and the OS is 64-Bit, does this mean a 64-Bit version of Visual Studio was installed? What may I try to get this resolved?
Thanks in advance, Kevin
June 27, 2013 at 5:54 pm
this link should help - it was written for SSIS 2008 R2 - and is applicable to SSIS 2012
this link also address SSIS 2012 connecting to Oracle 64 bit via ODAC
July 2, 2013 at 5:32 am
Thanks prvmine. Installing the Oracle 32-Bit Client resolved the issue. Just wondering why Microsoft has not come up with 64-Bit Development Tools (BIDS, VSS, etc.)?
Thanks, Kevin
July 2, 2013 at 11:24 pm
Glad you resolved the issue Kevin.
No ideal about MS moving to 64-bit development tools, but wish they would.
- prv
July 3, 2013 at 3:59 am
kevinsql7 (7/2/2013)
Thanks prvmine. Installing the Oracle 32-Bit Client resolved the issue. Just wondering why Microsoft has not come up with 64-Bit Development Tools (BIDS, VSS, etc.)?Thanks, Kevin
I don't think VS will move to 64-bit soon.
So you better always install the 32-bit and 64-bit clients on your machine.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply