The Adventures of SSIS and Oracle in 64bit Land..!

There's plenty of information out there spread out amongst the web concerning migration of packages to a 64b bit platform. I thought in the interests of fellow professionals out there I should share some of my experiences.

Firstly installation. Look at the C: drive on a 64bit Windows Server 2003 installation and you'll notice 2 "Program Files" folders one of which is suffixed (x86). Any 64bit applications are installed in the Program Files folder and 32bit applications are installed in the Program Files (x86) folder. So far so good and pretty simple but this is where you will encounter the first of several problems getting Oracle connectivity to behave on a 64 bit server.

Before I go into any detail here's a list of things that you need to know when you have installed 64bit SQL Server on an x64 server.

• Visual Studio (BI Dev Studio) is a 32bit application.
• SQL Server Management Studio is a 32bit application.
• dtexecui.exe is a 32bit application.
• dtexec.exe has both 32bit and 64bit versions.
• There are x64 and x86 versions of the Oracle provider available.
• SQLAgent is a 64bit process.

OK with all this information let's begin. Firstly if you develop packages on a 32bit platform, which is likely, and you are connecting to Oracle, use the Oracle OLEDB Provider, NOT the Microsoft provider for Oracle. For starters there is no 64bit version of this and secondly MS has no plans to create and support one.

Now you're using the Oracle OLEDB Provider we should be able to avoid a few problems later on although I would like to make note of one problem I had but couldn't find a resolution for. In a project I'm working on we needed to execute an Oracle stored procedure before we could access the data. When we ran the procedure using the MS provider (MSDAORA) everything was fine but as soon as we moved onto the Oracle provider (Oracle.OLEDB.1) the package would just hang as soon it attempted to access Oracle. This magically rectified itself when we moved servers. Just letting you know so beware.

Launching the 64bit Oracle universal installer will provide you with four options. Instant client, runtime, custom or administrator. Now I am not any kind of Oracle expert so I didn't go near the "custom" option and stuck with the other three default selections but I could only get the Administrator install to provide me with a working registered Oracle provider. Now this may work for someone else but on this server, only Administrator worked.

With the Oracle provider installed and my connection string configuration using the Oracle.OLEDB.1 provider there's a series of interesting little test you can run. First create a package that just sucks some data out of an Oracle table and places it in to a row count data flow task. We don't want to do anything, just get the Oracle end of this process working. Execute the package in the following ways;

1. Double click on the package and execute.
2. Execute it through the command line interface (using the dtexec.exe in C:\Program Files (x86)\Microsoft SQL Server\..\dtexec.exe".
3. Execute it through the command line interface (using the dtexec.exe in C:\Program Files\Microsoft SQL Server\..\dtexec.exe".
4. Execute it as a SQL Server job.

The interesting thing is that the first two methods of executing the package will fail and the second two will be successful. Why? Well this is because a 32bit process cannot see the 64bit drivers. The first method initiates dtexecui.exe which is a 32bit process the same as the dtexec.exe in the (x86) program files folder. Running the process using dtexec.exe in the normal program files directory or as a SQL Server job executes the package as a 64bit process which quite happily sees the 64bit Oracle drivers.

This can prove to be a problem if you want to debug your package as Visual Studio is 32bit and can't see the Oracle drivers meaning you'll get messages about things not being registered even though you know you did. Fortunately you can install the 32bit and 64bit Oracle drivers on the same machine meaning each process type then has the ability to see the Oracle drivers. Run the same four methods of executing our test package and the same thing will happen. The first two will succeed and the second two will fail.

This issue is down to a problem with the Oracle provider. The Oracle provider appears to have a particular dislike of being used by processes that have any mention of a bracket, Program Files (x86) being one of them. So even though you have correctly installed the 32bit Oracle providers on the server, you can't use them. The solution, copy the Microsoft SQL Server folder and Visual Studio folder into a new folder on your C: drive called ProgramFilesx86 and all of a sudden you development environment can see the Oracle drivers and work. You need to use the dtexecui.exe in there as well if you want to interactively execute packages.

Remember though this is only an issue if you need to perform any debug of packages that use Oracle providers on a 64bit server. Quite a specific problem but quite common none the less.

Now you've got over that problem solved(ish) you can start to build beautiful complex packages utilising every control and data flow task available to you. But then you get strange errors. Ones that are centered around script tasks especially BUT not always.

Personally I've had a couple of issues moving packages onto 64bit servers, excluding the Oracle problems mentioned above. First script tasks sometimes need to be recompiled on the 64bit hardware. This is kind of hit and miss but appears to be common in scenarios where you have a template package and you copy and change the definitions in data flows. The recommendation is open up each package and script on 64bit to be on the safe side although I have had a number of occasions where we've had to delete the script task and recreate it get the same code working correctly. Very odd.

This issue doesn't seem to be isolated to script tasks though. Execute package tasks seem to sometimes have similar behavior where reopening the package on the 64bit hardware corrects it.

Overall the benefits of SSIS and 64bit are obvious but be careful during development and if you can't develop in 64bit then try to at least have a server that is 64bit for testing this kind of thing and if you do get any kind of strange issues cropping up, try to bear in mind the apparent inconsistencies in the two platforms.

Update:

Corrected some spelling mistakes and disgustingly bad grammar..!

Technorati tags: SSIS, Oracle, 64bit, Provider, OLEDB