Disclaimer: This post pertains to Business Intelligence Development Studio 2008 (aka BIDS, SSIS, Integration Services, as well as SQL Server Data Tools since then) . I have not tested this method with newer versions, so please share your experiences in the comments.
When Microsoft made the switch from Office/Excel 2003 to Office/Excel 2007, they made some drastic changes. Most notably, the XLS file extension went away, replaced by the XLSX (Excel XML), XLMS ((Excel Macro-Enabled XML), and XLSB (Excel Binary) file format types. XLSX became the default, while XLSB became the successor to XLS in a sense, as both use binary under the hood. XLSM now because the only format that allows macros.
Under the hood, Microsoft switched from the Jet Database engine to ACE (Access Database Engine. 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).
Apparently, BIDS didn’t get the memo (and 6 years later, still doesn’t have this), because selecting Microsoft Excel 2007 in the Excel version drop-down allows you to only pick the XLSX extension. In the explorer browser, switching your Files of Type to All, and trying to open XLSM or XLSB file, you get this nasty message:
Fortunately, I was able to find a workaround.
1) First, create an XLSX copy of your workbook and point your Excel Connection Manager to it to gather the meta data
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 should be it! One caveat is that if you want to delete the XLSX workbook, you will have to delay validation on any containers connecting to your workbook, as it will try to validate against the deleted XLSX file. Also, the XLSX file doesn’t have to contain all of your data – just enough that SSIS can pick up the headers.