I created SSIS will do task like get data from oracle to sql server.i run ssis package run in my local system.it is working fine but i deployed ssis package in remote system and trying access from sql procedure. I'm getting error like below.
Oracle client and networking components were not found. These components are supplied by Oracle Corporation and are part of the Oracle Version on 7.3.3 or later client software installation.
Please let know if any solution there?
Simplest solution: The Oracle client is not installed on the remote server where the SSIS package is being executed.
Slightly less simple solution: The Oracle client is installed on the remote server, but in the wrong bit-count for the SSIS installation. For example, if the 64-bit Oracle client is installed but SSIS is being executed with the 32-bit dtexec executable, SSIS will not be able to find the Oracle client.
The solution in this case would be to install the 32-bit Oracle client side-by-side with the 64-bit client.
1.Go to My Computer Properties
2.Then click on Advance setting.
3.Go to Environment variable
4.Set the path to
F:\oracle\product\10.2.0\db_2\perl\5.8.3\lib\MSWin32-x86;F:\oracle\product\10.2.0\db_2\perl\5.8.3\lib;F:\oracle\product\10.2.0\db_2\perl\5.8.3\lib\MSWin32-x86;F:\oracle\product\10.2.0\db_2\perl\site\5.8.3;F:\oracle\product\10.2.0\db_2\perl\site\5.8.3\lib;F:\oracle\product\10.2.0\db_2\sysman\admin\scripts;
change your drive and folder depending on your requirement...
Technology used: Windows 7, UFT 32 bit, Data Source ODBC pointing out to 32 bit C:\Windows\System32\odbcad32.exe, Oracle client with both versions installed 32 bit and 64 bit.
What worked for me:
1.Start -> search for Edit the system environment variables
2.System Variables -> Edit Path
3.Place the path for Oracle client 32 bit in front of the path for Oracle Client 64 bit.
Ex:
C:\APP\ORACLE\product\11.2.0\client_32\bin;C:\APP\ORACLE\product\11.2.0\client_64\bin
In my case this was because a file named ociw32.dll had been placed in c:\windows\system32. This is however only allowed to exist in c:\oracle\11.2.0.3\bin.
Deleting the file from system32, which had been placed there by an installation of Crystal Reports, fixed this issue
After you install Oracle Client components on the remote server, restart SQL Server Agent from the PC Management Console or directly from Sql Server Management Studio. This will allow the service to load correctly the path to the Oracle components. Otherwise your package will work on design time but fail on run time.
Source: Stackoverflow.com