December 9, 2021 at 2:36 pm
Hello,
So basically I have an SSIS package that fails when I use an older version of excel (xls). I cannot change the way the excel file is loaded since it is being generated by and old system. Is there a nice and easy (haha), way of converting an (xls) file to (xlsx) in SSIS? I would like to add this step to my SSIS package.
Thank you all in advance for your assistance!
Here is my error message:
Error: Opening a rowset for "excel tab$" failed. Check that the object exists in the database.
The are no problems, only solutions. --John Lennon
December 9, 2021 at 2:58 pm
Hello,
So basically I have an SSIS package that fails when I use an older version of excel (xls). I cannot change the way the excel file is loaded since it is being generated by and old system. Is there a nice and easy (haha), way of converting an (xls) file to (xlsx) in SSIS? I would like to add this step to my SSIS package.
Thank you all in advance for your assistance!
Here is my error message:
Error: Opening a rowset for "excel tab$" failed. Check that the object exists in the database.
No there is not. You would be better off switching your connection type to access XLS rather than XLSX.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
December 9, 2021 at 3:33 pm
Hi Phil,
Nice to hear from you. I will try this. I hope you have a great holiday and stay safe!
The are no problems, only solutions. --John Lennon
December 9, 2021 at 3:52 pm
Hi Phil,
Nice to hear from you. I will try this. I hope you have a great holiday and stay safe!
Thank you very much! If you have trouble getting this working, please post back and I will try to remember what needs to be done – I have not had to import from an XLS file for quite a while.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
December 9, 2021 at 5:35 pm
One option you could do (probably not ideal, but a possibility) would be to use something like the scripting agent in SSIS to open the file in excel (hidden window) and then save it with an xlsx extension. I say not ideal as that means you need to have a licensed copy of Excel on the same box hosting SSIS, and excel is not a "quick to load" application, so the SSIS package will likely slow down.
Or, another approach, you could tackle it in powershell and have the 3rd party application write to a specific folder, then the powershell script would fire on a schedule before the SSIS job to convert all the xls to xlsx. Again, not ideal because excel is not quick to load.
And, if like Phil said, it is just a setting on the SSIS side of things, converting the xls to xlsx feels like overkill.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
December 13, 2021 at 8:59 pm
Hello SSC,
This issue has been resolved, but it had nothing to do with the excel version. There was a step in the SSIS package that was renaming the excel file incorrectly which would fail the process. The error message was completely off, which sent me on a wild goose chase.
Error: Opening a rowset for "excel tab$" failed. Check that the object exists in the database.
The confusing part was that this did not happen all the time. It depended on the date/time stamp that was being appended to the file. So, file: Excel_01012021.xls was renamed to Excel_010120212.xls. This failed the process.
So if anyone runs into the issue, check to see if the excel file is being renamed or moved to a different location in your process. I was stuck on this for 2 days, hopefully this post will save you time.
Have a great holiday, folks! Stay safe!
Dave
The are no problems, only solutions. --John Lennon
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply