Thursday, 17 September 2009

SQL Server Integration Services 2008 64 Bit frustrations

We recently needed to purchase new infrastructure for our data warehousing environment. As we aren't likely to do this for a while, we decided to bite the bullet and go 64 bit everything; OS & SQL Server 2008. This has been a much harder than were envisaged and given how straight forward 64 bit UNIX is, somewhat disappointing.

The first issue we became aware of is there are no 64 bit excel drivers available yet. Apparently they will arrive in Office 2010, which given we are using Office 2003 at the moment, won't arrive here for another 6 years. This has meant for about 1/2 our packages they will only work in 32 bit mode.

Next we found that BIDS only runs in 32 bit anyway, so if we addressed the above issue, we would have problems maintaining these packages anyway. This pretty much says to me that at least from an SSIS point of view, 64 bit SQL Server is not really for prime time.

Finally we had a lot of problem installing 32 bit ODBC drivers. They installed fine, but we then couldn't find them in the odbc administrator. It turns out their are 3 (maybe more ) obdc administrators in Win Svr 2K8 64bit. Solution: Using the odbcad32.exe located in %windir%\syswow64 .

I have seen the Microsoft adds that state the WOW starts now. Maybe they were referring to that directory...

NB: This process is ongoing, so I will update this post if we discover any more gotchas...

1 comment:

Innovator said...

Hi Leigh,

This was posted on the CORTEX in response to your post:

I sympathize as the last platform I built suffered similar frustrations with vendor promises and realities. In my case it wasn't Microsoft but SAP.

To save costs, we opted for unix and 32 bit versions of the software. Then came a time to upgrade to 64 bit and we were spun a line that all we had to do was upgrade x. Once we did this we found that several other bits of the vendors software had to be upgraded (at a cost) as well.

We definitely got the impression that full disclosure was missing from the early discussions.

In my opinion this strategy by the vendor may earn them dollars in the short term but they sure make me think twice about ever buying their software again.

The good news from this bad story was that it gave us the impetuous to look at alternatives to compliment our half 32 half 64 bit platform. I found that there were lots of open source and other vendor products that could fill the gap much more cheaply.

Good luck anyway!