Problem:
I was recently tasked with creating an SSIS package to pull data from a 32-bit version of Windows Server 2003 with an installation of Oracle 9i Database into a 64-bit version of Windows Server 2003 with an installation of SQL Server 2005. On the surface this seemed like a simple task since SSIS has built in Oracle data connections. However, this proved to be a lot harder than I could have ever imagined.
After initially having trouble connecting to the Oracle 9i Database I took some time to setup a test environment as shown below:
No matter what I tried, I could not seem to get the 64-bit environment to connect to the Oracle 9i Database on the 32-bit environment.
Upon further analysis (and hours of searching on the web), it seemed that the major issue with getting the 64-bit environment to connect to the 32-bit environment with Oracle had to do with the fact that within a 64-bit environment SQL Server installs in two separate program file folders, both the regular 64-bit, "Program Files", as well as the 32-bit, "Program Files (x86)", shown below:
After many more hours of reading and testing I came to the conclusion that the Oracle 9i Database had an issue connecting to directories that include special characters, including "(" and ")", and in my case those found in "Program Files (x86)".
Problem Analysis:
All in all, I eventually found that in order to gain connectivity from a 64-bit installation of SQL Server 2005 to a 32-bit installation of an Oracle 9i Database one of the two following scenarios had to be fulfilled:
1. Oracle patches must be applied to both servers in order to take care of the issue with 64-bit versions of windows using parenthesis in the “Program Files (x86)” directory. The server with the Oracle 9i database would need to be upgraded to the Oracle 10g Database in order for the patch to be applied. According to my client this was not a viable option.
2. The machine with the 64-bit version of SQL Server 2005 would need environment variables and the registry edited in order to allow connectivity without using directories with parenthesis. This would be a temporary fix in a development environment. In a production environment, this change would potentially have to be done each and every time an update was applied to the server. According to the client this was not a viable option either.
Even though I had come up with two ways to successfully connect, I still needed to find a way that would be appropriate given my restrictions from the client.
Solution:
In order to successfully connect the 64-bit installation of SQL Server 2005 to a 32-bit installation of an Oracle 9i Database an “Intermediate” server or virtual machine had to be configured. Below are the names and configurations that were used in order to gain connectivity from the Oracle 9i Database to the 64-bit version of SSIS:
1. Source Server – Server 2 (Windows 2003 32-bit w/ Oracle Database 9i)
2. Intermediate Server – Server 3 (Windows 2003 32-bit w/ SQL Server 2005 32-bit)
3. Destination Server – Server 1 (Windows 2003 64-bit w/ SQL Server 2005 64-bit)
Since I had a limited ability to change the configurations of Server 1 and Server 2 (shown above), the solution below only makes configurations to the Intermediate Server as described below:
1. Installed a 32-bit version of Windows 2003.
2. Installed a 32-bit version of SQL Server 2005.
3. Installed the Oracle 10g 32-bit client (10.2.0.1.0)
Note: Without the Oracle client connectivity to the Oracle Database was still not possible.
4. Installed an upgrade to the Oracle 10g client using Oracle Patch #4547817 using the previously installed Oracle Universal Installer (this upgrade must be installed in the same Oracle Home as the initial Oracle Installation)
5. Copied the tnsnames.ora file from the Oracle 9i Server location (C:oracleora92networkadmin) to the Intermediate Server location (C:oracleproduct10.2.0client_1NETWORKADMIN) in order to setup the connection strings on the Intermediate Server.
6. Opened SSIS on the Intermediate Server and created an OLE DB Source using the Microsoft OLE DB Provider for Oracle to connect to the Oracle 9i database (ensure that the “AlwaysUseDefaultCodePage” property for the OLE DB Source is set to “True”). This pulled the data from the Oracle 9i database to a SQL Server database on the Intermediate Server.
7. Created an OLE DB Destination with a SQL Native Client provider to the Destination Server. This pushed the data from the Intermediate Server to the Destination Server.
8. Ran the package on the Intermediate Server and the data was successfully pulled from the Oracle 9i database and onto the 64-bit Destination Server.
Conclusion:
Gaining connectivity from a 64-bit installation of SQL Server 2005 to a 32-bit installation of Oracle is not as easy as it would seem. Without adding/patching the Oracle client to the machine with the 64-bit installation of SQL Server 2005 and upgrading/patching the machine with the 32-bit installation of Oracle, connectivity was not possible.
By using an “Intermediate” server or virtual machine connectivity proved to be a breeze. There did not seem to be any hitches pulling data from Oracle into the 32-bit instance of SQL Server, or pushing the data from the 32-bit instance of SQL Server to the 64-bit instance.
