SSIS Read Oracle on Windows 64bit Server

We recently had a project reading data from a Oracle database using 'Microsoft OLE DB Provider for Oracle'. But since both of our database and OS are in 64bit version, we usually encoutered an error below:

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.

Problems

Because BIDS is a 32 bit application, as well on 32 as on 64 bit machines, it cannot see the 64 bit driver for Oracle. Because of this, connecting to Oracle from BIDS on a 64 bit machine will never work when you install the 64 bit Oracle client.
Another problem is the "Microsoft Provider for Oracle", this driver only exists in a 32 bit version and Microsoft has no plans to create a 64 bit one in the near future.

Solution

In order to make it works on BIDS when we are developping, we can update the debug options of project properties.

 

In our project, we use SQL agent job to invoke SSIS packages. At the beginning, we tried several approaches to resolve this problem and spent considerable time to search the solution on the internet, but nearly most of them don't work or very complicated to achieve.

 Finally, effort not to long, I find a config of the agent job settings, which can let SSIS packages run on 32 bit runtime.

 

 

 

转载于:https://www.cnblogs.com/ITPCBI/p/3498480.html