Oracle connection issues from SSIS

Question

Getting connection error on oledb source from the SSIS package to import the data from oracle to sql server. It is failing after deploying to server but runs fine when being run from BIDS from local machine. The package is developed in 2008.

Is there any installation needed on server for oracle?

The following error is being displayed

Source: Connection manager Description: SSIS Error Code DTS_E_OLEDB_NOPROVIDER_64BIT_ERROR. The requested OLE DB provider MSDAORA.1 is not registered -- perhaps no 64-bit provider is available. Error code: 0x00000000. An OLE DB record is available. Source: "Microsoft OLE DB Service Components" Hresult: 0x80040154 Description: "Class not registered". Code: 0xC020801C OLE DB Source [1] Description: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager failed with error code 0xC0209303. There may be error messages posted before this with more information on why the AcquireConnection method call failed. Code: 0xC0047017 Source: SSIS.Pipeline Description: component "OLE DB Source" (1) failed validation and returned error code 0xC020801C. End Error Error: 2019-06-18 22:54:18.99 Code: 0xC004700C Source: Pipeline Description: One or more component failed validation.

When I am validating the package, there it is giving ORA-01017: invalid username/password; but the same credentials are used while running from local machine.

I am running it in 32 bit but still the error persists Also tried with ProtectionLevel as Dontsavesensitive but no luck

Answer 1

You need to install Oracle drivers on the production machine. If I remember one way to do it is to install the x64 Oracle client software for Windows. Its enormous but it seems to work. This has worked for me.

According to this stackoverflow answer you can manually install the drivers but its a lot of steps:

https://serverfault.com/questions/175257/how-to-install-oracle-ole-drivers-for-use-in-sql-server-2008-r2-x64

Answer 2

Yes, you need to install an Oracle client. The bare Instant Client should be sufficient as you use the deprecated MSDAORA OLE DB provider from Microsoft.

If you like to use the Oracle Provider for OLE DB (OraOLEDB.Oracle) then you must install the provider. It is not included in the Instant Client.

Ensure that the architecture (i.e. 32-bit or 64-bit) is the same as your application. Your question is not clear in that. You write "I am running it in 32 bit" but the error message "The requested OLE DB provider MSDAORA.1 is not registered -- perhaps no 64-bit provider is available." indicates 64-bit.

Note, old MSDAORA provider from Microsoft exist only for 32-bit. If you need a 64-bit version then you must take the provider from Oracle.

32-bit drivers can be downloaded from 32-bit Oracle Data Access Components, the 64-bit drives are available at 64-bit Oracle Data Access Components