February 16, 2016 at 12:46 pm
Receiving error "Oracle Home not found" running package on Windows Server 2012 / SQL 2014 Enterprise SSIS w/ SSDT (VS 2013) trying to SELECT rows from SQL Server and INSERT rows into Oracle using Oracle 12c client
February 16, 2016 at 12:53 pm
Resolution is a 2 step fix as you'll need to add the Windows Logins and/or A/D groups that are invoking the SSIS pkg that are INSERTing rows into Oracle (requires administrative rights on the server):
STEP 1)
- Identify the Windows Login(s) and/or A/D groups that have execute permissions on the SSIS Package
- add the Windows Login(s) and/or A/D groups that are invoking the SSIS pkg to the Oracle Names under Local Users and Groups
To do so:
- OPEN: Administrative Tools, Computer Management, Local Users and Groups, Groups
- Scroll down and locate the Oracle Names (we had 7 Names like ORA_CLIENT_LISTENERS, ORA_DBA, etc)
- Double click each ORA_xxxxx name (1 at a time) and ADD the Windows Login(s) and/or A/D groups that will be running the SSIS Pkg
- click APPLY and SAVE
STEP 2)
- locate the high level base install directory for Oracle (ours was C:\Oracle)
- right mouse click the Oracle folder, properties, Security tab
- Edit, ADD the same Windows Login(s) and/or A/D groups that execute the SSIS Pkgs that INSERT into Oracle (we gave FULL control)
NOTE: We had to wait 1-2 minutes for some reason -- for the new security permissions to take affect -- there was NO reboot required
February 18, 2016 at 12:47 pm
This whole thread confirms my supposition that Oracle is the bastion of all evil and decreptitude.
February 20, 2016 at 5:50 am
Hi,
Oracle does tend to make things more complex than it really Needs to be..... 🙂
The solution I used is to use the TNS_ADMIN variable to store the location of TNSNAMES.ORA. Within this file is the Location of ORACLE_HOME. When SSIS uses the Oracle Client to Access the database, TNS_ADMIN will automatically be looked up to find what it Needs.
May 18, 2020 at 12:31 pm
The ORACLE_HOME is not referenced in the tnsnames.ora. Could you specify what you mean?
I am having a similar problem (oracle 18c XE with CDM, VS 2017 professional). I can connect to the oracle by creating an oracle source and can preview the data that the select query will bring in but there is a Oracle Home not found query when the package is executed. I have made sure that I only have the 32 bit drivers and have specified that the package be execute as a 32bit package but it still doesn't work.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply