I have a web scraper written in Python, fetching raw data from the HTML of a page and writing it onto a 97-2003 Workbook Excel file, using the Xlwt library. I then have a .dtsx file with some tasks, where one of them is an Excel Source task to fetch data from an Excel file. Later down the road, that data is inserted into a SQL Server table.
If I try to access my newly-generated Excel file with said task, I get an OLE DB error
External table is not in the expected format
And I cannot run my dtsx. However, if I manually access the Excel file through my File Explorer, open it and close it again (don't even need to save it), suddenly my SSIS task works without a problem, fetching all the columns and all the info. What could possibly be causing this behavior?
External table is not in the expected format
The error above happens when the Excel file is corrupted and cannot be opened by Access Database Engine (OLE DB provider) even if you can open the file from Excel.
In general, the solution is to open this Excel manually which will auto repair it. In a similar case and if the process is repeated many times, you can automate opening and repairing excel using a C# script using Interop.Excel library.