Exporting to Excel 2007 - .xlsx vs. .xlsb

Exporting to Excel 2007 - .xlsx vs. .xlsb

  • Comments 10

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 7 and 7 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?

  • This doesn't work if you do not have the ACE driver, which does not come standard with SQL Server/SSIS.  Or you can use some other add-on.  I could be wrong, but from everything that I've researched, out-of-the-box SSIS can't export to an .xlsx file.  Very frustrating :(

    Ben - You probably don't need this answer anymore but you can set the connection manager to overwrite the file, or you could even do a Execute SQL Task and use EXCEL connection type and do a 'drop TABLE [worksheet name]', then add the worksheet back in a second Execute SQL Task after you've deleted the old worksheet.  I had to manually create some Excel worksheets because a TEXT(sql server data type) that I was trying to drop into an Excel spreadsheet that required me to hard-code the column to LONGTEXT.  SSIS overwrites the metadata to VARCHAR(255) by default.  Hope this info helps someone somewhere out there.  I spent A LOT of time researching multiple issues related to an Excel destination in SSIS, it's not fun.  

    www.josefrichberg.com/.../working-with-excel-destinations-in-ssis-0xc0202025-0x80040e2.html

Page 1 of 1 (10 items)