Exporting to Excel 2007 - .xlsx vs. .xlsb

Exporting to Excel 2007 - .xlsx vs. .xlsb

  • Comments 9

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"

Leave a Comment
  • Please add 4 and 6 and type the answer here:
  • Post
  • Thanks for this tip. I was about to go crazy trying to export 73,000 rows. I thought there wasn't any support for a true XLSX file.

  • Thank you for your information, but I want to know how and where to set the Excel Connection Manager’s connection string property?

  • Still doesn't work after making this change.  I opened up my SSIS package, clicked on "Connection Managers," then checked "DestinationConnectionExcel" and changed the Connection String as indicated above.

    Unfortunately, I still get the error.  Am I missing something?

  • Bonnie - you can set the connection manager's connection string in the Properties window (press F4 to make it display).

    mtCG - You need to hit Enter to commit the connection string changes. Do you perhaps have an expression on the connection manager that is resetting the value? Are you exporting to an existing file, or creating a new workbook with the destination?

  • Thank you Matt, you DA MAN...how were we supposed to figure this out anyways? or how did you ??

  • I have this magic ability called "Access to the source code" :) It helps to figure these things out.

  • Hello Matta,

    thanks for your post.

    When I did that I am getting an error message like this while connecting to a excel destination.

    TITLE: Microsoft Access Database Engine

    ------------------------------

    Could not find installable ISAM.

    Can you please help me.

  • Thanks a ton Matt, it saved my day!

  • Hi, this works great thanks!

    The only thing I can`t find anywhere, is how to empty the XLSX file before using it as a destination.

    With a normal table, I would use TRUNCATE TABLE, but it doesn't work.

    Do you have a suggestion beside having an empty file that I copy to clear the XLSX destination file?

Page 1 of 1 (9 items)