Recently I had a strange issue with an Excel Source in a SSIS package.
While developing the SSIS package everything worked perfectly. Even after the initial deployment to an Acceptance stage and various user tests everything was working fine.
Until one day. Shortly after we got a new excel source data file. When the package was executed by a developer it worked well. But when a business user started the package it ran into the follwing error.
….Description: Opening a rowset for “Sheet$” failed.
Check that the object exists in the database…..
Of course there was a sheet in the excel file named “Sheet”.
It took nearly a whole day of reserach until we found the real problem behind.
Best help was this blog post here :
Strange Error Loading Excel Files (xlsx) using SQL Server Integration Services
The reason for failing is that the package is executed in the context of the windows user (which is intended in our case) and the business (windows domain) user does not have any permission on a general temp folder on the server which the MS Access/Excel driver is utilizing when a excel source file is getting larger than a specific threshold.
So you can fix that problem by granting write permissions on the folder
C:\Users\Default\AppData\Local\Microsoft\Windows\Temporary Internet Files\Content.MSO
to all users executing any packages with Excel Sources.
This needs to be further up on google for sure. Solved my problems after about 6 hours of troubleshooting!
Superb!! I was clue less on the issue..but this solved the problem!! Thanks a lot!
Took me forever to actually fix the issue. Our SQL Server is on Windows 2012 and the Temp directory needed is C:\Windows\Temp. None of the others work. If it is different than this you can use $env:TEMP in powershell to find the correct one.
Hi I had the same error, i share the folder, E:\FolderName –> \\Server\folderName
It’s amazing. I lost hours trying solved it. Thanks a lot!