Friday, May 27, 2011

SQL Server Destination Vs OLE DB Destination and 64-bit Oracle Drivers

For some reason, SSIS team dropped the ball when dealing with 64-bit and SQL 2005.  Cryptic errors and non-working packages, and command-line workarounds were everywhere.  SQL 2008 has a “use 64-bit” option when scheduling the package.  However, there are still challenges with 3rd-party drivers.

Here’s a workaround to get Oracle 64-bit drivers going.

* Install Oracle 32 and 64 bit drivers, 11g i think, maybe first install 64 bit then 32 bit
* Install Attunity Oracle provider 64 bit
* Copy Visual Studio directory to new dir: C:\ProgFilesX86\

Should work now both in designtime (BIDS) and runtime (SSIS). Oracle is sensitive to () in the path of the calling app so everything under Program Files (x86) will fail. After normal installation of SQL Server, simply copy VS dir and start devenv.exe from there.

Use Attunity:s .NET provider in SSIS. Really fast and stable.

SQL Server Destination Vs OLE DB Destination

No comments: