Monday, February 05, 2007

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: , , , ,

18 comments:

Anonymous said...

You saved my life!!!! Thank you! Thank you!

Steve said...

I knew there was a reason why I started doing this blog thing :)

Greg said...

Great article...definitely the most comprehensive I have found. I've gotten SSIS to work (thanks to you), but cannot figure out how to get my legacy DTS packages working. Do you have any advice?

When I run SqlWb.exe under the new program files directory (minus the parenthesis - x86) and try to open up a legacy DTS to change its Oracle connection I get an error (due to the change in directory I imagine).

If I run SqlWb.exe in the regular (x86) directory, I can't get access to the Oracle drivers as your article explains.

Steve said...

As a wild stab in the dark, the problem seems to always stem from how you execute the initial DTS package. What you could try is creating an SSIS package that has a execute DTS package task in there then running that under the different scenarios...

If that doesn't do the trick then come back to me and I'll try and dust off some of my SQL 2000 books :)

S├ębastien said...

How do you execute a stored procedure with a ssis / Oracle and Cursor ?

Steve said...

Are you trying to execute an Oracle stored procedure with a cursor in or are you trying to execute multiple Oracle procedures with a SQL Server side cursor?

Anonymous said...

Thanks for a very helpful post, Steve. I've taken your ideas and run with them a little bit:

http://www.artisconsulting.com/Blogs/tabid/94/EntryID/5/Default.aspx

Arpan said...

Steve,
I have developed a SSIS package on a 32 bit machine and then deployed it on to a 64 bit SQL Server which has a 32 bit VS2005 and SSIS. Now when I open the packages on the 64 bit server I can connect to the oracle box the test connection succeeds. But when I try to run the package or under the SQL Server Job it ERRORS out stating that The 'OraOLEDB.Oracle.1' provider is not registered on the local machine.

I can execute the package using dtexec but I really want to execute them under a scheduled job.

What can I do to execute the package in SQL Job Agent on the 64 bit server.?

Steve said...

Arpan,

Install the 32 and 64bit Oracle providers on your server. You are trying to run a 64 bit process when you have only the 32 bit provider on the server. This will work when you run it interactively but fail when running a job.

All the information you need is in the post.

Steve

Laks said...

Steve

I have a SQL Server 2005 installed in a Itanium X64 server. I have installed the Oracle 10g Client. I am able to connect to Oracle using Oracle SQL+PLus, but when I try to connect using the Import/Export wizard it does not work. I get an error saying 'Oracle Clients are not installed'. I cannot see the OraOLEDB Oracle driver in the drop down.
Any suggestions.
Regards
Laks

Steve said...

Laks,

The import / Export wizard will be a 32 bit application I would imagine so you'll need the 32bit oracle provider installed for it to be seen in the list. How that behaves on Itanium though, I'm not sure.....

Chris said...

This was freaking brilliant! We never would have figured this out on our own (and we're pretty smart if we do say so ourselves). We were dealing with a 64 bit server and everything worked fine using oracle except SSIS through visual studio. Great job writing it up as well. Very well thought out. Much obliged!!!!!

pranmala said...

I am running into issues with Oracle provider in 64 bit with SQL2008 SSIS.

Do the workaround apply to SQL2008 also?

Steve said...

I haven’t explicitly looked into this issue with SQL Server 2008 but I can’t imagine Microsoft have changed their stance on developing 64 bit providers for Oracle. If you have all of the tools installed on your 64 bit system have a look in the Program Files and Program Files x86 directories and you should be able to determine which applications are 32 or 64 bit then use the logic in the blog post to go from there.

Adam Tappis said...

Hi Steve,

Nice article mate. Should help me sort out our server configuration.

Cheers,
Adam.

Steve said...

No problem. How are you doing anyway

DC said...

Steve: Thanks for this blog.. I got it working using this method..
http://forums.oracle.com/forums/message.jspa?messageID=2583801#2583801

Question now.. do you know if Oracle fixed what they needed to do a workaround in 10g client in the 11g client?

Steve said...

I think there's a comment further up that talks about the Oracle driver problems. They've gone in to more detail here... http://www.artisconsulting.com/blogs/greggalloway/Lists/Posts/Post.aspx?ID=11