Reading Excel files into SSIS on a 64 bit OS

Update: Daniel Esser noted the same solution in his post SSIS Goodie #1: Excel + SSIS + 64 bit = DTS_E_OLEDB_EXCEL_NOT_SUPPORTED ? noting a classic code suck for why the Excel Source doesn't work:

It seems that Microsoft implemented an hard if-statement which throws an exception if DTS-Engine runs in 64 bit environment

SSIS has a built in "Excel Source" for reading data out of Excel files, but it has a variety of problems in a 64-bit environment (such as Windows Server 2008), the main one being that the source relies on the Excel Connection Manager, which in turn relies on the old JET engine OLE DB driver, which doesn't run in 64-bit mode. Googling for something like ssis excel 64 bit will give you lots of results and a variety of advice, mostly falling into these three flavors:

• run the package in 32-bit mode
• use the newer ACE OLE DB driver (released with Office 2010), and
• create an SSIS package using the SQL Server Import Wizard (that then can't be edited using BIDS)
• connect indirectly to the Excel file via SQL Server Update: Hrvoje clarifies that he's specifically tackling the situation where you cannot install a driver which is bit-size-compatible with your BIDS package.

I'll let you decide for yourself if any of those solutions are acceptable, but they are not in fact the only solution. Here's how I've done it instead:

1. download and install the ACE OLE DB driver
2. create an OLE DB Connection Manager (not an Excel Connection Manager) using that driver and referencing the Excel file
3. create an OLE DB Source (not an Excel Source) using that connection manager

This approach seems to be working fine and avoids most of the drawbacks of any of the other three approaches. Here's a screen shot of the Connection Manager Connect settings.

And the All tab, here the Extended Properties are important, Excel 8.0 covers older (.XLS) files, Excel 12.0 is used for newer (.XLSX) files. HDR indicates whether the first row contains column names, my spreadsheet didn't but yours may.

The sadly broken zoom function in Visio 2010

I've been using Visio for years to do a variety of software related diagramming tasks (and occasional home improvement projects), it really seems to be the only credible product in the niche between graphic programs like Illustrator, presentation tools like PowerPoint, and full fledged CAD or CASE systems. Anyhoo after a long break from using any Windows apps at all I just started using Visio 2010 for some database design work and was baffled/annoyed by the fact that it randomly adjusts the zoom level to something other than what I set it at, e.g. on my main monitor if I attempt to set it to 100% it changes the value to 104%. Thinking it had to be a bug I discovered that this is actually intended behavior, and there is no trivial work-around. Instead the only "easy" option is to create a macro that executes this VBA command

ThisDocument.ZoomBehavior = visZoomVisioExact

which you'll then have to run by hand every time you open the file (any if you want this behavior to be global you'll have to add the macro to every type of file). If you want this to happen automatically you're actually invited to create your own COM add-in. Just so you can zoom. Seriously Microsoft? Think the guys at The Omni Group would ever suggest that to a user?

Server-Side View Source for Rails

I often find, particularly early in projects, that I need to spend more time looking at the HTML source of a page rather than the page itself, e.g. when trying to get the correct javascript or css files included. All browsers offer some sort of "view page source" option, but this opens another window, and there's no way to directly refresh the page as you update it. After a few iterations I'm usually left with more than one source window open and no easy way to tell which is the latest. This problem had me thinking about a server side view source solution, and thanks to Rails controllers' after_filter it turns out to just take one line:

Throw this in a controller and instead of the normal pages you'll see the source in your browser, e.g.

Covers my basic need: no extra clicks to refresh, no getting lost in multiple popup windows, and since it's one line easy to turn on/off by commenting out. However in one important regard it's step backwards from Firefox's View Page Source: it lacks syntax highlighting. I had a couple thoughts about how to tackle this, and Google revealed some earlier attempts to pretty-print HTML in Ruby. I toyed with this approach for a few minutes before deciding it was heading way down the too-much-complexity path, particularly for a hacky development-only feature. Taking another look at that blog post something struck me--there's nicely formatted code right there in the post, thanks to Alex Gorbatchev's client-side javascript/css syntax highlighter. Alex kindly offers a hosted version of the needed files, which allows for extending the one line approach without adding any dependencies.

Which yields the much prettier result

Since it's no longer so easy to turn on and off via comments (Ruby's lack of a multi-line comment form is one of my main gripes with the language) I threw in a hack to turn it on and off by tacking VS onto the end of the URL (it should usually be harmless to do this as an extra param, e.g. ?VS or &VS).