ORACLE_HOME Error When Testing Connection with Oracle Connector by Attunity for SSIS

Question

I'm having the same problem. Here is my setup...

1. Windows 10 64-bit
2. SQL Server Data Tools v10.0.40219.1 SP1
3. Attunity Oracle Connector for SSIS (32-bit and 64-bit) v2.0
4. Oracle client 64-bit installed v12.1.0
5. Oracle client 32-bit installed v12.1.0
6. Setup tnsnames.ora to point to dev and QA instance; verified connectivity via tnsping and sqlplus.
7. Setup ORACLE_HOME to point to 64-bit product directory

I've created a (MSORA) Microsoft Connector for Oracle by Attunity connection. I populate the connection with a tns-name, username, and password. I click the Test Connection button, and I see the following:

The full error is as follows:

===================================

Error at Export SingleFile [Connection manager "Sample"]: Oracle Home not found.

 (Microsoft Visual Studio)

===================================

Test connection failed

===================================

Oracle Home not found.
 (Export SingleFile)

------------------------------
Program Location:

   at Microsoft.SqlServer.Dts.Runtime.ConnectionManager.AcquireConnection(Object txn)
   at Attunity.IntegrationServices.DataFlowUI.OracleUI.OraConnectionDialog.testConBtn_Click(Object sender, EventArgs e)

===================================

Oracle Home not found.
 (Export SingleFile)

------------------------------
Program Location:

   at Microsoft.SqlServer.Dts.Runtime.Wrapper.IDTSConnectionManager100.AcquireConnection(Object pTransaction)
   at Microsoft.SqlServer.Dts.Runtime.ConnectionManager.AcquireConnection(Object txn)

If its not pulling oracle_home from my system variable, where does it pull it from?

Here is the ORACLE_HOME environment variable on my system:

Answer 1

There was an issue documented in the post Attunity Oracle Conenctor doesn't work after update, which identified a compatibility issue between version-5.0 of the Oracle connector and the latest version of SSDT (15.9.x as of 02/01/2019). Until the compatibility issue has been resolved, the de-facto solution is to deploy the 15.8.0-release of SSDT. This will install the 14.0.3002.113 Microsoft SQL Server Integration Services Designer.

Here were the steps I executed to resolve:

1. Install oracle 32-bit client from software center
2. Install oracle 64-bit client from software center
3. Deploy tnsnames.ora file to 32-bit install
4. Deploy tnsnames.ora file to 64-bit install
5. Microsoft SQL Server Data Tools release-15.8.0
6. Install (32-bit) SSIS Oracle Connector by Attunity version 5.0
7. Install (64-bit) SSIS Oracle Connector by Attunity version 5.0
8. Verify you have read and execute rights to items in both 32- and 64-bit client directory

Here are shots from my environment...

Cheers, Adam