Data Access Technologies

(Data Access, XML, SSIS, LINQ, System.Data ...)

SSIS: Error 0x80070020 Unable to access the package file. Make sure the file exists and you have permission to access it.. The process cannot access the file because it is being used by another process.

SSIS: Error 0x80070020 Unable to access the package file. Make sure the file exists and you have permission to access it.. The process cannot access the file because it is being used by another process.

Rate This
  • Comments 2

This is my second blog and it is about a very interesting scenario that I came across when working with a SSIS package developer.

The design of the package consist of one main parent package having several "Execute Package Task" that calls multiple child packages from the file system location.

The actual issue here was that whenever we execute this package in the Business Intelligence Development Studio (BIDS) environment, we get the following list of errors displayed with either of the child package failing.

Errors:

Error 1: Error 0x80070020. Unable to access the package file, "C:\{package}.dtsx". Make sure the file exists and you have permission to access it.

Error 2: Error 0x80070020 while loading package file "C:\{package}.dtsx". The process cannot access the file because it is being used by another process.

This error is not specific to a single child package, but rather happens on any of the child packages. We went ahead and reproduced the issue and it failed with the same list of errors.

Errors:

Error: Error 0x80070020. Unable to access the package file, "C:\ABCD.dtsx". Make sure the file exists and you have permission to access it.

Error: Error 0x80070020 while loading package file "C:\ABCD.dtsx". The process cannot access the file because it is being used by another process.

In the BIDS environment, we did see that the Execute package task specific to "ABCD.dtsx", ran successfully even though the error pointed to that task and also we noticed that the next package down the lane failed to execute. We weren't sure why the error message was misleading.

So the first thing that we performed was to check if we had permissions to access these folders and files. So yes we did have all the required permissions to access. Since we are accessing the package located on the same system and not on any network share, we ruled out the access permission denied issue as this point of time.

But to be absolutely sure we went ahead to check the file system activity using Process Monitor utility. We ran the process monitor utility and reproduced the issue by executing the package again.

Note: To download Process Monitor utility visit: http://technet.microsoft.com/en-us/sysinternals/bb896645

Analysis of Process Monitor:

We went through the Process Monitor traces collected. We filtered the traces specific for "Process Name" is "DtsDebugHost.exe" since we are executing the package from the BIDS environment and we are more interested in them.

Note: DtsDebugHost.exe is the execution host used by Business Intelligence Development Studio (BIDS) when executing a package from the designer in debug mode, which is the default behaviour.

When we searched for the "ABCD.dtsx" file we hit upon a specific entry that failed with “SHARING VIOLATION" result.

1:54:41.0720131 AM DtsDebugHost.exe 3176 CreateFile C:\ABCD.dtsx SHARING VIOLATION Desired Access: Generic Read, Disposition: Open, Options: Synchronous IO Non-Alert, Non-Directory File, Open No Recall, Attributes: n/a, ShareMode: Read, AllocationSize: n/a

image

clip_image004

We performed a research on this "SHARING VIOLATION" result that we had from the process monitor traces. But there weren't lots of article about this online. This article by Mark Russinovich, "Mark Russinovich: The Case of the Temporary Registry Profiles” throws some light to this "SHARING VIOLATION" result.

Taken from the above article:

"When a process opens a file, it specifies what kinds of sharing it allows for the file. If it is writing to the file it may allow other processes to read from the file, for example, but not to also write to the file."

So we understood that this "SHARING VIOLATION" simply means that another process had opened the file (Package file) in a way that was incompatible with the way that the BIDS process (DtsDebugHost.exe) wanted to open the file.

But from the process monitor traces we don't find any entries of any processes accessing the target package file (ABCD.dtsx in this case) except for the intended DtsDebugHost.exe file.

So we decided with the following action plan:

Action Plan:

1. Give full permissions for "Everyone" to the folder from where DTSX files are available.

2. Set all the child packages to run out of process and check the behaviour.

3. Check on the antivirus program if they are accessing the files.

4. If none of this works get an AV (Access violation) dump for this process.

So we started with the first action plan, went till the second one and determined that none of this actually helped us. Each time we ran the package, we hit upon the same list of error messages and in the process monitor that we had collected; we had the same old "SHARING VIOLATION" error.

It was time for us to change the approach, so this time we took an in-depth analysis of the process monitor without applying any filters to the traces.

In-Depth Analysis of Process Monitor traces:

From the process monitor traces we determined that as soon as we get these "SHARING VIOLATION" we had the Antivirus process completing the scanning operation.

image

* The process names are removed on purpose.

So we suspected the Antivirus program to use these files. So we decided to disable the Antivirus program and check the status. But since this was a corporate environment we didn't have the necessary privileges to disable the Antivirus program.

So we settled up with stopping the services specific to the Antivirus program. We had about 3 different services of the Antivirus program running which included the real-time scan process as well. We disabled all these services and executed the package again.

And this time to our total surprise, not only our package executed without any issues but also the total execution of the package which would typically take about an hour for completion executed in just 20 minutes.

The Antivirus program was actually locking the files and that was the reason for the "SHARING VIOLATION" result we had in the process monitor traces. So once these processes were disabled, the packages executed successfully. So when we hit upon a SHARING VIOLATION we would need to check for the subsequent processes that follow this entry and investigate on this to determine if any of these processes are accessing the target file.

So yes, the Process Monitor utility had been put to perfect usage here and one more case has been nailed down using this utility.

 

 

Author : Krishnakumar(MSFT), SQL Developer Engineer, Microsoft

Reviewed by : Smat (MSFT), SQL Escalation Services, Microsoft

Leave a Comment
  • Please add 4 and 5 and type the answer here:
  • Post
  • Very helpful blog. We had the same problem and your information help me to solve my problem. Thanks for sharing.

  • Thank you so much!  This issue has plagued me for months.  Now I FINALLY know the reason!

Page 1 of 1 (2 items)