I was asked to take a look into why an SSIS package that is running through SQL Server 2012 isn't working properly. I opened the package and saw that the error is due to an inability to read the .xlsx file.
As a test, I opened the file manually, saved it as an .xls file, and changed the connector to read it as a 97-2003 excel file - and it worked fine. I resaved that .xls as an .xlsx (thinking maybe the original was just broken) and the 2007 connection fails again.
Looking at the error logs, I see one error that is:
Hresult: 0x800004005 Description: "Could not find installable ISAM"
Searching around I'm not finding much luck. Does anyone know what the problem here is? I validated that the permissions on the file are correct, it is not locked by another process, the path is correct. Not sure what else I can do. I'm guessing it's related to this archaic server that is running this with SQL Server 2012.
Connection string is: Provider=Microsoft.ACE.OLEDB.12.0;Data Source=\\<my server>\shared\<the folder>\<filename>.xlsx;Extended Properties="Excel 12.0 XML;HDR=NO";
The solution was that the ACE redistributable was either damaged or corrupted. I couldn't even uninstall it, I had to repair it through the add/remove programs, then uninstall it, then download the latest 32 bit version and install, and that fixed it. The installed version also didn't seem to match the microsoft version either.
I downloaded Microsoft Access Database Engine 2016 Redistributable from here: https://www.microsoft.com/en-us/download/details.aspx?id=54920
Version: RTM Date Published: 8/11/2020
Installed the 32 bit version.