Error in initializing provider. ORA-12154: TNS:could not resolve service name while connecting to Oracle from SQL Server in SSAS, SSRS, DTSExec 64-bit

Error:
SQL 2005 64bit Installed on Windows 2003 64bit Connecting to Oracle 10g 64 bit using Oracle 10g client 64bit throws below errors while connecting from SSIS or Visual Studio or Reporting Services or Analysis Services

Error 1: Error in initializing provider. Catastrophic failure

Error 2: Error in initializing provider. ORA-12154: TNS:could not resolve service name

Error 3: Not all properties can be set. Window Handle (BAD VALUE)

Root Cause:
64-bit Microsoft OS install's 32-bit applications into the following location
"C:\Program Files (x86)\..."

Same 32-bit application when installed in 32-bit OS the typical location will be
"C:\Program Files\..."

Notice that there is an extra bracket in the path, This causes an existing networking bug to occur in Oracle Client driver where the networking layer is unable to parse program locations that contain parenthesis in the path to the executable which is attempting to connect to Oracle Database.

Solution:
We can use the Short Name Naming option available in Windows to overcome this problem. There is also another option of copying the "Visual Studio Folder" to C:\ and then design the package but it is not suggested!

Below image shows you what is short name path for Visual studio folder:


Sol 1. Create two batch files in C:\ with contents as
"start /B "C:\Progra~2\Microsoft Visual Studio 8\Common7\IDE" "C:\Progra~2\Microsoft Visual Studio 8\Common7\IDE\devenv.exe"" - Name it as "Start BIDS.bat"

"start /B "C:\Progra~2\Microsoft SQL Server\90\Tools\binn\VSShell\Common7\IDE" "C:\Progra~2\Microsoft SQL Server\90\Tools\binn\VSShell\Common7\IDE\SqlWb.exe"" - Name it as "Start Mgmnt. Studio.bat"

I assume that your visual studio is installed in C:\. You can replace devenv.exe with dtsexec.exe or whichever exe like TOAD which is creating this connectivity issue...

Now double click the batch file to open the visual studio and now if you test the connection, It will work perfectly!

Instead you can also use NTFS Junction Point to create a short path like C:\VS to point to C:\Program Files(x64)\Microsoft Visual Studio 8\Common7\IDE\) and then use C:\VS\devenv.exe here in the batch script. Download NTFS Junction Point

(OR)

Sol 2. If you have access to metalink then you can download below patches
5059238 - Patch for (x86) issue
http://updates.oracle.com/ARULink/PatchDetails/process_form?patch_num=5059238

4751549 - "opatch"
http://updates.oracle.com/ARULink/PatchDetails/process_form?patch_num=4751549

Also once you have created the package and when you trying to schedule it, you will get the same error... So what you can do is copy the command line from the job created and then create a new job will below parameters and replace the command line in the below sytax with command line copied from created job
xp_cmdshell 'C:\PROGRA~2\MICROS~2\90\DTS\Binn\DTExec.exe ....command line'

How to get shortnames for Windows Directory?
Go to CMD and then for whichever the folder you want the shortname type
DIR PROGR*.* /x
This will give you the lengthy name and short name for the folders!

Comment here incase you are still unable to resolve this issue!

Links:
1. The Adventures of SSIS and Oracle in 64bit Land..!
2. Oracle Drivers on an x64 Box for SSIS Development
3. Connecting to Oracle on 64-bit (x64) machine
4. Configuring Connection from MS SQL 2005 (x64) to Oracle 10g (x64) on Windows 2003 Server (x64)
5. How To Troubleshoot an ASP-to-Oracle Connectivity Problem(Some of steps are general)
6. Workaround for Oracle Connectivity Issues with SSIS on X64
when deploying Package developed in 32-bit