tisdag 13 mars 2012

SSIS, Oracle and x64

Originally posted by 2009 me at another blog.

SSIS, Oracle and x64

I hope I can spare someone the time I just spent to get this working...

The scenario is as follows:
We have SSIS 2008 installed on a windows 2008 x64 server and want to connect to a Oracle database and the standard drivers doesn't work. Something with version 7.3.3 requiring another set of drivers. Anyway, here is what I did. (You don't have to download the complete 1,87 GB package)

I chose to go through ODBC.

1. Download Instant Client Package - Basic (~40Mb) from Oracle (11g) for both 32 and 64-bit.

x64 found here:
http://www.oracle.com/technology/software/tech/oci/instantclient/htdocs/winx64soft.html
and 32-bits found here:
http://www.oracle.com/technology/software/tech/oci/instantclient/htdocs/winsoft.html

2. At the same location, download Instant Client Package - ODBC (1Mb) for both 32 and 64-bits.

3. I started with the 64-bits install. Create a folder somewhere, i.e. C:\Oracle\InstantClient_64 and extract the files from the basic package (64-bit) to that folder. Extract the ODBC package (64-bit) to the same catalog. Open a commandprompt and run odbc_install. Be sure to start the prompt with administrator privileges.

4. Now we need som .ora files, sqlnet.ora and tnsnames.ora was enough for me. I copied them from another server. You will find lots of examples on the internet if you try searching google. I created a folder C:\Oracle\InstantClient_64\network\admin and placed them there.

5. Now we need to create some enviroment variables. Theese are shared between 32 and 64 bits enviroment. First add to the Path variable the directory you just created (C:\Oracle\InstantClient_64). Second create a new variable called TNS_ADMIN pointing to the location of you .ora files (C:\Oracle\instantclient_64\network\admin)

6. Run the ODBC-administrator with administrator privileges and try creating a system-dsn, it should work. The problem is that BIDS is a 32-bit application, so you can't see the 64-bits odbc sources from BIDS.

7. Create a folder somewhere, i.e. C:\Oracle\InstantClient_32 and extract the files from the basic package (32-bits) to that folder. Extract the ODBC package (32-bit) to the same catalog. Now here is the thing: open a commandprompt from C:\Windows\SysWOW64\cmd.exe and run odbc_install.exe. This installs this as a 32-bits driver.

8. Add the directory containing 32-bits package to the path variable (C:\Oracle\InstantClient_32)

9. Run 32-bits ODBC-administrator from: C:\Windows\SysWOW64\odbcad32.exe and verify that it works.

10. When you create the DSN, make sure you call them the same name in both 32 and 64 bits. Also, clear the username and make sure it's a system dsn.

11. Done.

Hope this will help someone, or me when this is long forgotten.

Inga kommentarer:

Skicka en kommentar