Excel Connection Error: "External table is not in the expected format"

  • Lidou123

    Hall of Fame

    Points: 3050

    I hope your are doing well.

    I've been breaking my head since yesterday. I hope you can help me.

    I have an error on the production server. SSIS can no longer connect to an Excel file. I have an error "external table is not in the expected format". I work with Excel 2010 32 bits and SSIS 2017 64 bits

    In the project, I have a dozen Excel source files (2003-2007 excel files) but there are only 2 files that are in error. -> What makes me crazy is that when I open the file, SSIS can connect to the file. I close the file and the connection goes wrong -> When I save the file in Excel 2010 version SSIS happens to connect.

    Do you have an idea to help me?

  • Jo Pattyn

    SSC-Dedicated

    Points: 31346

    I did have the same problem with excels exported from SSRS. Haven't tried to fix it beyond opening the excel and saving it again with excel

    • This reply was modified 1 month ago by  Jo Pattyn.
  • anthony.green

    SSC Guru

    Points: 112354

    Who or what is the source of the excel files?  Could it be where the source is has exported them as 2013 office format or above?  If it's a 3rd party then your going to have to re-work the connection strings for the updated providers.

     

    Office 2010 goes out of support this year, highly recommend you start the upgrade process and sharpish to ensure everything is tested and signed off, otherwise your going to leave yourselves open to potential attack surfaces by using out dated software.

  • Phil Parkin

    SSC Guru

    Points: 243990

    Can you confirm that all of the files are in .XLS format?

    Probably unrelated, but I saw this error when I tried to open a .XLSB file using a (ACE) .XLSX connection string.

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply