Yesterday I had to load some data from Oracle database to my SQL Server 2008 R2. And as many of you my laptop runs 64 bit version of Windows. Task seems to be simple. When you try to use Oracle Ole DB for the first time from Business Intelligence Development Studio, you’ll get message that you have to install the driver first. So why it’s looks as installed??? Ok, I have to get Oracle Ole DB driver for Windows
So I get my driver from Oracle, and there’s no 64 bit version, you can get only 32 bit. Now simple thing, you have to just create source and destination in simple data flow and that’s all. We press F5 and bazinga! We get: Error: SSIS Error Code DTS_E_OLEDB_NOPROVIDER_64BIT_ERROR. The requested OLE DB provider OraOLEDB.Oracle.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
I saw different solutions, but not the simplest one. Check this print screen:
Go to your SSIS package project properties. And find option “Run64BitRuntime”. Set it to false and now when you press F5 your project will run without error. This is the best solution in my opinion when you can run your package from BIDS.
In case when you have schedule it you have to use 32 bit version of dtexec utility. By default SQL Server use 64 bit. So you will find it at: your SQL Server drive Program Files (x86)Microsoft SQL Server100DTSBinn
Then you can run your package with something like this:
dtexec /FILE “C:UsersUserNameDocumentsVisual Studio 2008ProjectsIntegration Services Project1Integration Services Project1Package.dtsx” /DECRYPT /CHECKPOINTING OFF /REPORTING EW
[…] few months ago I wrote a post about problems when you use 32 bit driver in 64 bit SQL Server. Now I had to face another problem linked to this solution. We made an upgrade of existing SQL […]