SSIS Excel connection error "External table is not in the expected format."

Problem

-I have an excel spreadsheet generated a lotus app. It smells and looks like excel but Excel Source data flow source can not recognise it. When trying to select a table(tab) i get the following error message "External table is not in the expected format.”

-Opening the excel spreadsheet and save it again helps(File also reduces in size). But as dev we ara allegic to manual processes.

-I have tried to change the connection string using a variable

from

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\DataImport\Lotus.xls;Extended Properties="Exc开发者_如何学编程el 8.0;HDR=YES";

To

Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\DataImport\Lotus.xls;Extended Properties="EXCEL 12.0;HDR=YES";

If i can avoid the script task that would be great

---

I have had to open and close the excel file manually saving. Those bugs that can't be fixed

---

I had the same error, your post led me to open the excel file but my excel file was corrupt. I'm posting in case this helps someone else.