#title SSIS - Exporting to Excel 2007 - .xslx vs. .xslb

http://blogs.msdn.com/mattm/archive/2010/04/05/exporting-to-excel-2007-xslx-vs-xslb.aspx

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 (.xslb). If you’re creating a new Excel file, and have given it an .xslx extension, you’ll get the following error when trying to open it in Excel:

Excel cannot open the file ‘xxx.xslx’ 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 .xslx 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.xlsx;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"