I can get the Attunity drivers to work fine, but I cannot get any of the Oracle connections to work to give me the ability to use the Execute SQL Task to run a delete statement.

I have installed both 32 and 64 bit ODAC drivers and the SQL tools. and while I can get a connection to TEST connectivity correctly, it fails on the acquire connection when it runs.

I have tnsname files in both 64 and 32 bit directories. I have put the paths to the client..bin folder in the PAth environment variable. I am at a loss. I've searched the net and only find 2012 examples and the ones I find have all seem to have different setups. So I think they just keep installing Oracle apps until it works.

Does anyone have a bulletproof install guide?

I have a SQL 2014 server and I am developing in Visual Studio 2013.

I think you need to download the SSIS 2014 Attunity Oracle adapters and then edit the package to use the new version to fix the issue.

A couple of recommendations.

1) Use EZCONNECT to bypass TNSNAMES and specify the connection details directly in the Connection Manager.

2) The Oracle client is OCI.DLL.  Attunity, OleDb, and ODBC are all just wrappers around OCI.  And OCI is a Win32 DLL, loaded with LoadLibrary().  So it will need to be found in the path.  Well on a 64bit machine, you must manipulate the PATH to ensure that 32bit programs find the 32bit OCI.DLL and 64bit programs find the 64bit OCI.DLL.

Your package can be configured to run in 32 or 64bit mode, but Visual Studio is _only_ 32bit.  So the proper configuration is to set the system path to include only the 64bit OCI.DLL (remember to reboot after this), and then start Visual Studio with a batch file that puts the 32bit OCI.DLL location in the path before launching Visual Studio.

David,

I don't understand why anyone used the Attunity driver over the Oracle OLEDB. I have found out that it does not allow me to run an Exceute SQL Task to run a Delete statement on the database. Once I found that out I did not see a reason to clutter the system with another install.

Now when you say I have to manipulate the PATH, are you talking about the environment variable: Path?

My path value: c:\Oracle64\product\12.1.0\client_1; c:\Oracle64\product\12.1.0\client_1\bin; C:\Oracle\product\12.1.0\client_1;

The way I read it is, I still need both 32bit and 64bit Oracle bits installed. I ran ODAC121021_x64 and ODTwithODAC121021x32 versions and installed the 32bit to C:\Oracle and 64bit to C:\Oracle64.

I do see the OCI.dll in the both the 64 and 32bit client_1 folders. 

In the last paragraph I am unsure what the system path is, is it different than the environment variable path is, that I posted above? 

And what is the batch file I need to start VS with that will put the 32bit OCI.DLL location in a path before launching Visual Studio? 

>In the last paragraph I am unsure what the system path is

It's a registry value that controls the PATH of processes on system startup.  And it's the only way to change the PATH for Windows Service applciations.  You change it in the "System" settings.