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.