Upgrading the Data Access Components for Analysis Services from Windows XP to Windows 7

I had a project where I needed to upgrade a .NET web application.

Before the upgrade, it had the following environment:

• Development: Visual Studio 2003, Windows XP
• Production: SQL Server 2000, Analysis Services 2000, Windows Server 2003
• Data Access: Microsoft Data Access Components 2.8 (MDAC 2.8) 

After the upgrade, it had the following environment:

• Development: Visual Studio 2008, Windows 7
• Production: SQL Server 2008, Analysis Services 2008, Windows Server 2008
• Data Access: Windows Data Access Components 6.0 (WDAC 6.0)

The first thing that you need to know is that MDAC 2.8 has been renamed to WDAC 6.0. While MDAC needed to be installed separately, WDAC is a core component of Windows Vista and later operating systems, which means there is no separate distributable for installing WDAC. In trying to minimize any potential code changes, I initially tried to reuse MDAC 2.8 by copying Interop.ADODB.dll and Interop.ADOMD.dll to the new environment and added them as references in my projects, but I got the following error:

The project currently contains references to more than one version of Interop.ADODB, a direct reference to version 2.8.0.0 and an indirect reference (through 'ADOMD.Member.Properties') to version 6.0.0.0. Change the direct reference to use version 6.0.0.0 (or higher) of Interop.ADODB.

I couldn't find anything on the web on how to fix this error, so after several hours of trying to get this to work, I decided that it would take less effort to just use WDAC instead. But it took me a few more hours of research to finally figure out how to add references to the new WDAC DLL's.

1. Select the Build -> Clean Solution menu
2. Delete all instances of Interop.ADODB.dll and Interop.ADOMD.dll in all the project directories (usually in the obj or obj/Debug folders).
3. For each project that uses the old MDAC 2.8 components, replace references to Interop.ADODB.dll to msado15.dll by right clicking on the project -> Add Reference, click on the COM tab, and look for Microsoft ActiveX Data Objects 6.0 Library as shown by the first highlighted line below.
4. For each project that uses the old MDAC 2.8 components, replace references to Interop.ADOMD.dll to msadomd.dll by right clicking on the project -> Add Reference, click on the COM tab, and look for Microsoft ActiveX Data Objects (Multi-dimensional) 6.0 Library as shown by second highlighted line below.
5. Do a full rebuild of the projects and all data access related code should magically compile without any code changes.

Configuring SSIS 2008 to Connect to Oracle

Connecting SSIS to an Oracle database is a lot more difficult than it really should be. There are a couple of reasons for this. The first is because BIDS only runs in 32 bit mode even if you have a 64 bit operating system, but SQLAgent will run as a 64 bit process in a 64 bit operating system. The second is that there is more than one way of getting it to work, so various sites will give seemingly conflicting information, which may be applicable for one case, but not another.

The following are the high level steps I used to get SSIS 2008 to connect to an Oracle database.

1. Install the correct version of the Microsoft Connector for Oracle by Attunity.

Go to the Microsoft Connector for Oracle v1.2 download page if you're using SSIS 2008.
Go to the Microsoft Connector for Oracle v2.0 download page if you're using SSIS 2012

Download the 32 bit version if you're using a 32 bit OS, and the 64 bit version if you're using a 64 bit OS. Note that the 64 bit version includes both the 32 and the 64 bit binaries.

2. Install the Oracle Instant Client. If you're using 64 bit Windows, you'll need to download and install BOTH 32 and 64 bit versions of the client software.

Get the 32 bit Oracle Instant Client here.
Get the 64 bit Oracle Instant Client here.

There were some folks on other sites who couldn't get the Oracle Instant Client to work with SSIS, and therefore advocated installing the full version of the Oracle database software instead. The downside with this of course is that you'll end up eating a lot more disk space and memory than what is necessary, not to mention the wasted software licenses. The above pages also contain links for downloading Sql*Plus, which I like to use for testing purposes.

The Oracle Instant Client packages do not come with their own install programs. You'll have to manually unzip them to directories of your choosing. If you're installing both versions, you should unzip them to different directories. I like adding x32 and x64 to the directory names so I can easily differentiate between them. Note that as of version 11.2, all these zip files unzip to the same subdirectories by default, so I would unzip them in the following order.

• Unzip Oracle Instant Client 32 bit
• Unzip Sql*Plus 32 bit
• Rename subdirectory by inserting _x32 to the directory name. In my case, the path is x:\oracle\instantclient_x32_11_2.
• Unzip Oracle Instant Client 64 bit
• Unzip Sql*Plus 64 bit
• Rename subdirectory by inserting _x64 to the directory name. In my case, the path is x:\oracle\instantclient_x64_11_2.

You also need to create your own tnsnames.ora file. Set the TNS_ADMIN environment variable to point to the directory where you the put tnsnames.ora, and add the Instant Client directory to the Path variable. I use the 64 bit version in the Path in my case, but the 32 bit version should work, too. It should be noted that if you're not familiar with tnsnames.ora, manually creating that file yourself is not trivial. It would be best if you can work with an Oracle DBA to create the file for you.

3. Configure the Microsoft Connector and the Oracle Instant Client.

Follow the instructions here for installing and configuring the Oracle Connector. It shows the registry entries needed to allow the Microsoft Connector to recognize the Instant Client, and how to add the Oracle Source / Destination to the BIDS Data Flow toolbox. If you have carefully followed the instructions here and in the linked pages, you should now be able to add your Oracle data flows into your package.