There are situations where you might get “Another process is using the file”, Cannot Open File or Data file. I had this message with one of my SSIS Packages. Everybody was tired with their analysis and I was Pulled in to diagnose this issue.

This File is loaded through a 3 step Process. The file is generated from Mainframe and ftp’ed using another process to SQL Server Shared Folder. From Where it is picked up by the SSIS Package and Processed.(All ETL is done HERE).

The Mainframe and the FTP Process were Ruled out by the Network Engineers and Mainframe DBA’s, So it was all in WINTEL (as Usual). My diagnosis Ran me through these Steps and wanted to share this will all the Eager DBA’s.

I tried Renaming the File first to see if it was really used by another process. Yes it would not let me rename the file.

I closed the SSIS Studio and also closed all the other Open Locations (being cautious). but that did not help.

I had 2 Choices: One to keep working on to diagnose the issue OR get a Quick Fix and load the File, so that things can start moving. I did the later part first.

Let us consider the File name to be InvXXXX011_02.txt

I Copied the File (CTRL+C) and Pasted, so as a “Copy of InvXXXX011_02.txt” was created. I renamed the File to something else, in this case it was InvXXXX011_02222.txt and change my SSIS connection to point to this new File. Also changed the configuration Table value to reflect this new file name and got the package Working.

Now, it was time to diagnose the File issue.. As i did before the rename of the original File did not work, so I had to call in the Mighty Process Explorer from SYSINTERNALS. I download it on the server in question.

Click on Find –> Find Handle or DLL

Write the name of the File: In my case it was InvXXXX011_02.txt. click Search.

In my case the File was listed in the Space below and i double clicked on the File to return to the Handle Window, from where did i right clicked and Close Handle. and the File was closed.

This time i tried Running the SSIS Package with the Original names and it worked, the SSIS Package Processed without any error.