How to verify that a file is accessible in windows command or powershell?

Question

We receive XML files from customers on a sFTP server. Then a SQL Server job picks them up and load them in database. However, it often fails with an error saying that a file is not accessible. That's because it's still being uploaded to our FTP server.

Unfortunately, the files can't be renamed by the customer after the upload is completed. We have many of them and it's not their priority.

I was wondering if there is any way to identify if a file has already been loaded.

This is a broadly-scoped question. There is no simple answer. — rory.ap, Jun 23 '15 at 17:49
You could try this SO response to [Powershell: Check if a file is locked](http://stackoverflow.com/questions/24992681/powershell-check-if-a-file-is-locked) — jradich1234, Jun 23 '15 at 17:54
By no means a solution, but a workaround: Have a job that first moves the file from the upload target folder to another folder on the same machine. If the move fails because the file is still being uploaded, no problem - you'll pick it up next time. The actual move process will just be tweaking a pointer in the file system, which will be quick, so your "move" job is far less likely to have a handle on the file when your "load them into database" job (which would be targeting the "move" job target folder) and cause it to fail.. — LDMJoe, Jun 23 '15 at 17:55
You could use a [`FileSystemWatcher`](http://stackoverflow.com/a/17303984/1630171) to trigger the import after the file was created. — Ansgar Wiechers, Jun 23 '15 at 19:23

score 0 · Accepted Answer · edited May 23 '17 at 11:58

Here is a simple function, based on second answer in this SO question Powershell: Check if a file is locked linked in comments by jradich1234.

function Is-FileReady {
    param ([parameter(Mandatory=$true)][string]$Path)

    $File = New-Object System.IO.FileInfo $Path
    if ((Test-Path -Path $Path) -eq $false){
        # file does not exist
        return $false
    }
    try {
        # try to open file for exclusive access - [System.IO.FileShare]::None
        $Stream = $File.Open([System.IO.FileMode]::Open, [System.IO.FileAccess]::ReadWrite, [System.IO.FileShare]::None)
        if ($Stream){  
            $Stream.Close() 
            # file is ready.
            return $true
        }
    }
    catch { 
        # file is locked.
        return $false
    }
}

Usage:

Is-FileReady "C:\temp\blah.txt"

True

How to verify that a file is accessible in windows command or powershell?

1 Answers1