May 29, 2012 at 3:49 pm
I have a 64bit windows 2008 server, SQL Server 2008 R2 64bit, Oracle 11gR2 64bit client and also ODAC11.2.0.3.0 installled.
The TNSNAMES file is setup,
DSNs are setup,
the linked servers to Oracle work fine,
SQLPLUS connects fine.
BIDs cannot connect to oracle data source. :unsure:
Before I installed the ODAC it threw an error message stating - "The specified DSN contains an architecture mismatch between the Driver and Application"
Now that ODAC is installed I get - "Test connection failed because of an error in initializing provider. ORA-12154: TNS:could not resolve the connect identifier specified"
Is it possible for BIDs to see a 64bit driver/provider? :blush:
May 30, 2012 at 12:44 pm
Sorry that I don't have a direct answer, but you may try using the Attunity drivers to connect with Oracle:
http://msdn.microsoft.com/en-us/library/ee470675(v=sql.100).aspx
Here's an excerpt from that page talking about using BIDS and the 64bit Oracle driver. Notice though that the page was written in 2009 so your mileage may vary:
"Additional considerations for 64-bit environments:
On 64-bit platforms (both x64 and IA64), you need only install the Attunity Oracle 64 bit connector for the appropriate architecture (x64 or IA64). You should not install the Attunity Oracle 32 bit connector alongside the 64-bit versions, because the 64 bit connector installs both 32-bit and 64-bit versions.
The Oracle 32 Bit client is required in order to develop packages on a 64-bit computer because SQL Server Business Intelligence Studio runs in 32-bit mode.
The Oracle 64 Bit client is required in order to run packages on a 64-bit computer.
If you run the package in 32-bit mode, you do not need the Oracle 64 Bit client.
In summary, if you want both to develop and to run SSIS packages on a 64-bit computer:
Install the Attunity Oracle 64 bit connector.
Install the Oracle 11G 32 Bit client.
Install the Oracle 11G 64 Bit client.
Restart the computer after the Oracle installations are complete.
You should be able to develop and run SSIS packages."
----------------------------------------------------------------------------
Sacramento SQL Server users group - http://sac.sqlpass.org
Follow me on Twitter - @SQLDCH
----------------------------------------------------------------------------
May 30, 2012 at 2:37 pm
Thanks, I did noticed those drivers listed on another post somewhere.
After looking into it a bit more this morning I realized that the ODAC did make the drivers available to BIDs but I needed to update my PATH environment variable to see the correct TNSNAMES. After I added the correct location to PATH, BIDs was able to make the connection. 🙂
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply