SSIS - "External table is not in the expected format" and .xls files

Question

In a SSIS ETL transformation I'm trying to load a certain .xls file. I have several identical files with similar schemas, and all of them work correctly. However this one file throws the "External table is not in the expected format" error. This is what I have tried:

• I already changed the connection string from Jet OLEDB to ACE OLEDB, no result.
• I changed the type of connection from Excel 97-2003 to 2007, no result.
• The connection manager is using a variable so I tried making another connection manager that directly loads the file. No result.
• I'm using a SQL query to read from the file, so I tried reading the sheets the file has. They don't load.
• I tried "Save As..." and used Excel 97-2003.
• I tried resaving the file.
• I tried restoring the original file.
• I checked the sheets the file has and compared them to one similar to it that does load. At least both have the same sheets.

What's weirder though is that the file loads correctly if I keep it open in Excel while it's loaded in SSIS. As additional information:

• The file has macros, but so do the rest of the files.
• My computer runs a 32 bit architecture.
• I'm using Visual Studio 2010 + SQL Server 2012.

What else could make this error trigger?

EDIT: Now I tried to set up an ADO.NET source and an OLEDB source for opening the .xls file. ADO.NET cannot "retrieve information from columns", and OLEDB gets its connection closed. Maybe there is a way to know when the reading of the excel file fails?

I also tried something extremely tricky: I checked the event visor and noticed there were some few errors, so I did a quick Google search and fixed it. It was linked to SSIS but fixing it didn't solve the error.

Answer 1

In the end I did something much simpler: I converted the files to .xlsx and made my connection manager an Excel 2007 one. Although I'll mark another answer if it's better than mine, I'll leave this for other people with similar problems.

Answer 2

I faced same issue in one SSIS packages, to solve this issue.

use below step.