August 28, 2020 at 12:35 am
I have an SSIS package that iterates over Excel files and it has been working fine without any issue for the past fortnight.
Recently, it has been failing when new files are added due to the 'External table not in the expected format'.
Nothing about the files have changed, but what I have noticed is if I open one of the files and then save it again (changing nothing) it will then read without issue by the SSIS package.
This is not a viable long-term solution, as we need to be automating this process and remove any manual steps like having to re-save files.
Is there any solution to this obscure problem?
August 28, 2020 at 1:06 pm
Are you changing the file extension from .xls to .xlsx, by any chance?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
August 30, 2020 at 9:57 pm
This was removed by the editor as SPAM
August 31, 2020 at 2:32 pm
So open the file and just close it does it ask "Do want to save changes"?
It shouldn't because nothing would have changed.
So when you say save it, do you mean "Save As" because just hitting save wouldn't do anything. And yeah do you change the extension?
Also could you take the file as is and don't even open it and process it in visual studio interactively? Do you simply get a validation error saying wrong format?
Also look into the source of the file, where does it come from? When we have excel as the input it comes from a person or organization and EVERYTIME we have an issue it because they changed something or didn't do something right. I'd go back to the source and find out. Just happened here last week when someone renamed a tab on a file we get from a vendor. The same file we've been getting for the last 2 years.
The other thought is the file getting the wrong extension. Maybe an xls file was saved as xlsx. The extension would call the correct program (on desktop), and maybe it opens and when you resave excel does the conversion of the document to the xlsx format. I just tried that and Excel says its the wrong format and won't open it.
I'd start by going to the source.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply