Microsoft access database engine cannot open or write file

Question

Microsoft access database engine cannot open or write file

komal145

SSCrazy Eights

Points: 9918
More actions
August 24, 2016 at 1:25 pm

#329676

I am trying to load the excel file from variable in for each loop and load the file to a table. It runs fine in the BIDS / VS .
When i try to run as a job , it says cannot find the file.
SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft Access Database Engine" Hresult: 0x80004005 Description: "The Microsoft Access database engine cannot open or write to the file ''. It is already opened exclusively by another user, or you need permission to view and write its data.".
I tried 64 bit to false. I tried to give all permissions to the folder from where i read the file. I still get the same error.
Need help.

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246979 More actions · Answer 1

komal145 (8/24/2016)
I am trying to load the excel file from variable in for each loop and load the file to a table. It runs fine in the BIDS / VS .
When i try to run as a job , it says cannot find the file.
SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft Access Database Engine" Hresult: 0x80004005 Description: "The Microsoft Access database engine cannot open or write to the file ''. It is already opened exclusively by another user, or you need permission to view and write its data.".
I tried 64 bit to false. I tried to give all permissions to the folder from where i read the file. I still get the same error.
Need help.

I've faced this many times. Either it's a permissions problem, or the file is locked.

To prove it, assuming this is not a production environment, try setting the credentials of the SQL Agent service to be your own, restarting the service, and then running the job again.

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

komal145 SSCrazy Eights Points: 9918 More actions · Answer 2

Hi Phil,

Thank you for your quick reply. I tried the sql agent permissions and it ran perfectly. I was banging my head from morning to fix this issue.

komal145 SSCrazy Eights Points: 9918 More actions · Answer 3

It runs fine with all the SQL agent owner when i diable to task which copies the file to a archive folder. This can be done in Dev enviornment ( both BIDS and dev server). But when I try as a job IN UAT , again the file gets locked. What can be done ,if we don't want the file to be locked?

Phil Parkin SSC Guru Points: 246979 More actions · Answer 4

komal145 (8/24/2016)
It runs fine with all the SQL agent owner when i diable to task which copies the file to a archive folder. This can be done in Dev enviornment ( both BIDS and dev server). But when I try as a job IN UAT , again the file gets locked. What can be done ,if we don't want the file to be locked?

Without seeing your processing logic, this is difficult to answer. I have many packages which process files and then move them to archive folders and I do not get locks ...

Can you provide a screen shot of your control flow?

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

komal145 SSCrazy Eights Points: 9918 More actions · Answer 5

Mine are xlsb files and they require retain same connection , as i am trying to load 2 xlsb files in foreach loop ...each file load process has sequence container .

Phil Parkin SSC Guru Points: 246979 More actions · Answer 6

komal145 (8/24/2016)
Mine are xlsb files and they require retain same connection , as i am trying to load 2 xlsb files in foreach loop ...each file load process has sequence container .

I thought that 'Retain Same Connection' applied only to OLEDB database connections? How are you doing this with Excel files?

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

Jeff Moden SSC Guru Points: 1004403 More actions · Answer 7

komal145 (8/24/2016)
It runs fine with all the SQL agent owner when i diable to task which copies the file to a archive folder. This can be done in Dev enviornment ( both BIDS and dev server). But when I try as a job IN UAT , again the file gets locked. What can be done ,if we don't want the file to be locked?

Again, probably a privs problem. Does the SQL Agent Service login have privs to do such a thing where you need to do it?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

komal145 SSCrazy Eights Points: 9918 More actions · Answer 8

In excel connection manager : Retain same connection manager : true.

komal145 SSCrazy Eights Points: 9918 More actions · Answer 9

Please see the pacakge hierachy. Let me know if i can cahnge anything with copying? As the move file should be locked but copying of file should work fine. I am copying file to archive folder successfully in dev but in UAT fails and throws error that the file is being accessed by another process.

Phil Parkin SSC Guru Points: 246979 More actions · Answer 10

komal145 (8/25/2016)
Please see the pacakge hierachy. Let me know if i can cahnge anything with copying? As the move file should be locked but copying of file should work fine. I am copying file to archive folder successfully in dev but in UAT fails and throws error that the file is being accessed by another process.

As it works in Dev, that suggests that the package logic is working.

So I still suspect permissions as being the problem.

Regarding the overall structure, personally I think that putting file operations (delete/move/rename) inside Foreach loops over-complicates things.

A script task can do these operations, en masse rather than one at a time (cf. set-based vs RBAR), in just a few lines of easy-to-understand code – and that's how I do it.

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

komal145 SSCrazy Eights Points: 9918 More actions · Answer 11

Also , since you worked on Excel source. I have a question , my excel source always looks for test files which i pointed in excl connection manager at first. It validates to check those files in folder and if it does not see that files it fails. In UAT while testing i have to copy those test files to the folder where it downloads other excel files. Is there any way around to work this , not to copy those files? please help.

Phil Parkin SSC Guru Points: 246979 More actions · Answer 12

komal145 (8/26/2016)
Also , since you worked on Excel source. I have a question , my excel source always looks for test files which i pointed in excl connection manager at first. It validates to check those files in folder and if it does not see that files it fails. In UAT while testing i have to copy those test files to the folder where it downloads other excel files. Is there any way around to work this , not to copy those files? please help.

Try setting the 'Delay Validation' property for the connection to true.

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

komal145 SSCrazy Eights Points: 9918 More actions · Answer 13

I already set the delay validation to be true. I just figured out that " validate metadata" property on excel source =false. In this case it will not throw error.

Can you confirm , you have to do same with your excel source ?