SSIS Connections to Excel

I thought that I’d have my first SSIS/SQL topic cover something simple, but poorly documented on the internets – how do you create an Excel Connection to an .XLSM or .XLSB file in Business Intelligence Studio (aka BIDS, SSIS, and Integration Services)?

The answer is rather simple, but first, a little background on the topic. When Microsoft made the switch from Office/Excel 2003 to Office/Excel 2007, they made some drastic changes under the hood.  Most notably, the XLS file extension went away, replaced by the XLSX (Excel XML), XLMS ((Excel Macro-Enabled XML), and XLSB (Excel Binary) file extension.  XLSX clearly became the primary file type Microsoft wanted you to use, while XLSB was the true inheritor of XLS under the hood, since both are binary.  XLS has the option of containing Macros, but now this has been split into its own file type – XLSM.  Aside from that, Microsoft switched from the Jet Database engine to ACE (Access Database Engine – should be ADE, but whatever).  As a result, BIDS 2008 has two Excel versions options for Excel connections: Excel 97-2003 and Excel 2007 (which technically includes Excel 2010 as far as I know).

The problem arises after you select the Micorsoft Excel 2007 for the Excel version and find that you are limited to the XLS and XLSX filetypes.  What if you want to connect to XLSM or XLSB?  You have no option to do so, and if you switch your Files of Type to All, and try to open the file, you get this nasty message:

Surely, this is a sign of the coming apocalypse, but luckily for us, there is a workaround.

1) Pick any old crusty XLS or XLSX file to connect to in the Excel Connection Manager
2) Go over to the Properties window
3) Find the ExcelFilePath property and edit the path to point to the desired XLSM or XLSB file.

That’s it!  It’s so easy, even grandma can do it!