March 4, 2025 at 1:40 pm
Good Afternoon.
I have been manually running an SSIS package on an adhoc basis, which populates an Excel file from a SQL Server Table. There is no apparent issue with the package, but often I get the following error:
"External table is not in the expected format"
And it resolves when I Shut Down and Re-Start the machine.
Sometimes it does not work on the first run after the Re-Start but maybe the second or third run. The SSIS Container dedicated to populating the Excel file, actually fills multiple Sheets/Tabs. And when it does fail, it fails randomly on a different Sheets/Tabs (instead the same one every time). And maybe on the very subsequent run of the entire package it will run completely fine and populate all the Sheets/Tabs (without having made any modifications to the package !).
I would appreciate advice on resolving this perplexing issue.
March 4, 2025 at 1:50 pm
Regarding the Excel file, is it empty when the job runs? If not, what does it contain?
I suspect the problem may be to do with the guesswork employed by the Excel driver when assessing the datatypes of the external columns.
March 4, 2025 at 4:44 pm
This issue is likely due to how the Excel driver interprets data types dynamically. If possible, switch to .xlsx format, as it's more stable.
Also, try adding IMEX=1 to the connection string to force text-based reading. Ensure the file isn’t locked by another process and check for mixed data types in columns, which can cause errors. If the problem persists, consider rebuilding the Excel file or exporting to CSV instead, as it avoids type inference issues.
March 4, 2025 at 5:01 pm
This issue is likely due to how the Excel driver interprets data types dynamically. If possible, switch to .xlsx format, as it's more stable.
Also, try adding IMEX=1 to the connection string to force text-based reading. Ensure the file isn’t locked by another process and check for mixed data types in columns, which can cause errors. If the problem persists, consider rebuilding the Excel file or exporting to CSV instead, as it avoids type inference issues.
IMEX=1 does not necessarily force datatypes to text. It forces columns to text if one of the top 8 rows (I think that's the default) in the column contains text. But if they're all numeric, the datatype will remain numeric.
CSV files are far easier to export to, I agree, but in this case multiple datasets are being exported to multiple worksheets, making the CSV idea rather cumbersome.
Regarding your comment about XLSX files being 'more stable' than XLS files, are you able to expand on that?
And finally … welcome to the SSC forums!
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply