It will work
fine if we execute the DTS package that transfers data from excel file to SQL
Server database, but the problem comes when it is scheduled to run in a regular
interval through SQL server agent job.
Error:
The Microsoft
Jet database engine cannot open the file G:\foldername\filename.xls'. It is already opened exclusively by another
user, or you need permission to view its data.
Cause:
The account
that runs the job will not be able to access the file
We should check
whether the user that runs the job must have the appropriate privileges to
access the folder\file and also the permissions to run the job.
But sometimes the
job will not work, even if the account has system administrator privilege to
run the job and full control to access the file,
The exact
reason will be the account under which the SQL server agent is running may not have
access to the file
Workaround:
Adding the SQL
Server agent service account to the folder or file under security and providing
full control permission to access the file will devise the DTS job run
successfully.
