0

I have a new dev machine, and I need to support legacy SSIS packages. The new machine is Windows 8 64 bit with a SSD primary drive (C:) and a regular HDD for data (D:). On it, I have installed BIDS 2008 R2 (and all the tricks to get it to work with TFS2010), as well as VS2010, ODAC 32 bit and 64 bit (I think??), Oracle Client (SQLDeveloper).

I can get the following to connect just fine to an Oracle database:

  • VS2010 with MSDAORA
  • VS2010 with ODAC
  • VS2010 with ADO.NET
  • Test connection using .udl file with Oracle OLE DB Provider (MS provider does not show up as an option here)
  • SQLDeveloper (Oracle's Client Tool)

What I CAN'T seem to do is get BIDS 2008 R2 to connect to Oracle using ANY method (MSDAORA, ADO.NET, ODAC).

The problem seems isolated to my machine, I can otherwise open, view, validate, connect and run these packages on other machines with no issues.

I have tried the following:

  • Ensured any/all TNSNAMES files are correct and identical (taking working copies from other machines, and let's not forget, this works fine in VS2010)
  • Created ORACLE_HOME and TNS_ADMIN Environment Variables and pointed them to various directories within D:\app\username\product\11.2.0
  • Checked registry manually to make sure no other references to ORACLE except for the entries in HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE
  • Unistalled/Reinstalled SQL Server 2008 R2 components
  • Every "solution" offered in every thread in every search I have tried
  • Thrown chicken bones and offered sacrifices to pagan deities

After each attempt at a fix, I still get the following error when trying to Preview a SQL Command in the Data Flow:

An OLE DB record is available. Source: "Microsoft OLE DB Provider for Oracle" Hresult: 0x80004005 Description: "Error while trying to retrieve text for error ORA-01019"

Yes, I know I need to drop MSDAORA in favor of ODAC, and if ODAC worked in BIDS 2008 R2 I would certainly do it. However, I can't get anything to connect to Oracle there (even though, as mentioned above, it connects fine in various other tools)

I have two "client_n" directories in D:\app\username\product\11.2.0. As mentioned, I have tried pointing my environment variables to various paths inside. It seems like the correct path for ORACLE_HOME should be just D:\app\username\product\11.2.0, correct?

Has anyone had any luck getting BIDS 2008 R2 to connect to Oracle while on WINDOWS 8 with VS2010 also installed? It seems to me like it's a simple Oracle connection issue, I just don't know enough about Oracle to troubleshoot.

Any other ideas to try?

Scott
  • 1
  • 1
  • 1
  • Can you TNSPING the server from the command prompt? – Ciarán Jan 11 '13 at 13:42
  • I get "is not a recognized command". So I added D:\app\username\product\11.2.0\client_1 (and _2)\bin to PATH variable, no dice. I then searched for "tnsping" on both drives, still no dice. – Scott Jan 11 '13 at 17:42
  • Really? How odd. I cant swear to this, but that would seem to me that Oracle isn't installed properly. It is also usually installed in C:\Oracle and not under app\username.May I suggest you remove any client you may think is installed, download it again from Oracle and re-install. – Ciarán Jan 11 '13 at 17:59
  • Thanks Ciaran... I am close to making the decision to wipe the machine and reinstall it all. I will try this first, however, see what it gets me. – Scott Jan 11 '13 at 18:19
  • One final comment, the Oracle OLEDB Provider is not installed by default. Make sure you select it when running the install. Good Luck ! – Ciarán Jan 11 '13 at 18:30
  • After uninstall/reinstall of all Oracle ODAC downloads (both x32 and x64), I can make PARTIAL progress until a reboot. Specifically, I can open, validate, view packages that connect to Oracle using MSDAORA, I can even press the "Preview" button in the data flow task source query and get a result set (which is definite progress!), but if I execute the task it still fails with a Provider error. Also, once I reboot, the ability to validate and preview from data flow goes away. In the end, I decided to fire up a Win7 Virtual Machine to do any Oracle-related SSIS development. – Scott Jan 15 '13 at 19:59

2 Answers2

0

BIDS is x32 (even on an x64 machine), so you will need to install the Oracle Client x32 edition, Full Install.

Mike Honey
  • 14,523
  • 1
  • 24
  • 40
  • By Oracle Client x32, do you mean Oracle Instant Client x32? If so, already done... If not, I do not find anything else from the Oracle Download pages that mention an Oracle Client, perhaps I have simply overlooked it or its buried under some other term. Is there some way to test that instant client install? I do not appear to have a x64 version installed. However, I am under the distinct impression that I need ODAC specifically to make SSIS connect, not an Oracle client. – Scott Jan 15 '13 at 19:54
  • AFAIK Oracle Instant Client and ODAC dont work with SSIS. The Oracle Client is buried and they move it around. Here's the page with the 32bit edition: http://www.oracle.com/technetwork/database/enterprise-edition/downloads/112010-win32soft-098987.html – Mike Honey Jan 16 '13 at 03:04
  • Thanks Mike. I did download and install from that link, and used the SQL Developer app from within that client install to test connectivity successfully. Same results however, BIDS 2008 R2 is still not working within Win8. For now, I am going to abandon ship and just work with my Win7 VM that I set up yesterday. FWIW on the Win7 VM, I simply installed both 32 and 64 bit ODAC and BIDS 2008 R2 works with no issues. – Scott Jan 17 '13 at 18:31
0

We were able to get it to work, though I believe that we installed too much software. (I have not been able to test it with only the ODAC drivers.)

  1. Install the Oracle Client 32 & 64-bit drivers.
  2. Install the Oracle ODAC 32 & 64-bit drivers.
  3. In BIDS use the native Oracle connection, and not the Microsoft driver.
Tequila
  • 844
  • 9
  • 19