Exporting to Excel 2007 - .xlsx vs. .xlsb
Published Mar 25 2019 02:37 PM 944 Views
Copper Contributor
First published on MSDN on Apr 05, 2010

Douglas blogged about this a while back , but it’s come up a couple of times in the last little while, so I thought I’d repeat it here.

The Excel 2007 default output format for the SSIS Excel Destination is Excel Binary Format (.xlsb). If you’re creating a new Excel file, and have given it an .xlsx extension, you’ll get the following error when trying to open it in Excel:

Excel cannot open the file ‘xxx.xlsx’ because the file format or file extension is not valid. Verify that the file has not been corrupted and that the file extension matches the format of the file.

If you want the Excel Destination to output a standard .xlsx file (Excel XML Format), you’ll need to tweak your Excel Connection Manager’s connection string property.

By default it will look something like this:

Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:\path\xxx.xlsb;Extended Properties=" Excel 12.0 ;HDR=YES";

Changing “Excel 12.0” to “Excel 12.0 Xml” will tell the provider to output in .xslx format instead.

Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:\path\xxx.xlsx;Extended Properties=" Excel 12.0 XML ;HDR=YES"

Version history
Last update:
‎Mar 25 2019 02:37 PM
Updated by: