title: Powershell - "The process cannot access the file because it is being used by another process"
body: <p>Below is a script that monitors a directory and its subfolders for deposited files.  Every 10 minutes or so, I look for new files and then match them against a database table that tell me where they need to be moved to - then it copies the files to a local archive, moves them to the locations they need to be moved to, and inserts a record into another database table with the file's attributes and where it came and went.  If there is no match in the database - or there is an script error - it sends me an email.</p>
  
  <p>However, since files are getting deposited to the directory constantly, it's possible that a file is still being written when the script executes.  As a result, I get the error <code>The process cannot access the file because it is being used by another process.</code> emailed to me all the time.  In addition, because I'm not dealing with the error up front; it goes through the loop and a false entry is inserted into my log table in the database with incorrect file attributes.  When the file finally frees up, it gets inserted again.</p>
  
  <p>I'm looking for a way to identify files that have processes attached to them; and skipping them when the script executes - but several days of web searches and some testing hasn't yielded an answer yet.</p>
  
  <pre><code>## CLEAR ERROR LOG
  $error.clear()
  
  Write-Host "***File Transfer Script***"
  
  ## PARAMETERS
  $source_path = "D:\Files\In\"
  $xferfail_path = "D:\Files\XferFailed\"
  $archive_path = "D:\Files\XferArchive\"
  $email_from = "SQLMail &lt;SQLMail@bar.com&gt;"
  $email_recip = [STRING]"foo@bar.com"
  $smtp_server = "email.bar.com"
  $secpasswd = ConvertTo-SecureString "Pa$$w0rd" -AsPlainText -Force
  $smtp_cred = New-Object System.Management.Automation.PSCredential ("BAR\SQLAdmin", $secpasswd)
  
  ## SQL LOG FUNCTION
  function Run-SQL ([string]$filename, [string]$filepath, [int]$filesize, [int]$rowcount, [string]$xferpath)
      {
          $date = get-date -format G
          $SqlConnection = New-Object System.Data.SqlClient.SqlConnection
          $SqlConnection.ConnectionString = "Server=SQLSERVER;Database=DATABASE;Uid=SQLAdmin;Pwd=Pa$$w0rd;"
          $SqlConnection.Open()
          $SqlCmd = New-Object System.Data.SqlClient.SqlCommand
          $SqlCmd.CommandText = "INSERT INTO DATABASE..Table VALUES ('$date','$filename','$filepath',$filesize,$rowcount,'$xferpath',0)"
          $SqlCmd.Connection = $SqlConnection
          $SqlCmd.ExecuteNonQuery()
          $SqlConnection.Close()
      }
  
  
  ## DETERMINE IF THERE ARE ANY FILES TO PROCESS
  $file_count = Get-ChildItem -path $source_path |? {$_.PSIsContainer} `
                | Get-ChildItem -path {$_.FullName} -Recurse | Where {$_.psIsContainer -eq $false} | Where {$_.Fullname -notlike "D:\Files\In\MCI\*"} `
                | Measure-Object | Select Count
  
  If ($file_count.Count -gt 0)
      {
          Write-Host $file_count.Count "File(s) Found - Processing."
          Start-Sleep -s 5
  
  
      ## CREATE LIST OF DIRECTORIES
      $dirs = Get-ChildItem -path $source_path -Recurse | Where {$_.psIsContainer -eq $true} | Where {$_.Fullname -ne "D:\Files\In\MCI"} `
                                                        | Where {$_.Fullname -notlike "D:\Files\In\MCI\*"}
  
  
      ## CREATE LIST OF FILES IN ALL DIRECTORIES
      $files = ForEach ($item in $dirs)     
          {
              Get-ChildItem -path $item.FullName | Where {$_.psIsContainer -eq $false} | Sort-Object -Property lastWriteTime -Descending
          }
  
  
      ## START LOOPING THROUGH FILE LIST
      ForEach ($item in $files)
          {
              ## QUERY DATABASE FOR FILENAME MATCH, AND RETURN TRANSFER DIRECTORY
              $SqlConnection = New-Object System.Data.SqlClient.SqlConnection
              $SqlConnection.ConnectionString = "Server=SQLSERVER;Database=DATABASE;Uid=SQLAdmin;Pwd=Pa$$w0rd;"
              $SqlConnection.Open()
              $SqlCmd = New-Object System.Data.SqlClient.SqlCommand
              $SqlCmd.CommandText = "SELECT F.DirTransfer FROM DATABASE..Files F WHERE '$item.Name.Trim()' LIKE F.FileName"
              $SqlCmd.Connection = $SqlConnection
              $DirTransfer = $SqlCmd.ExecuteScalar()
              $SqlConnection.Close()
  
              If ($DirTransfer) # if there is a match
                  {
                      Write-Host $item.FullName"`t-&gt;`t"$DirTransfer
                      $filename = $item.Name
                      $filepath = $item.FullName
                      $filesize = $item.Length
                          If (!($filesize))
                              {
                                  $filesize = 0
                              }
                      $rowcount = (Get-Content -Path $item.FullName).Length
                          If (!($rowcount))
                              {
                                  $rowcount = 0
                              }
                      $xferpath = $DirTransfer
                      Run-SQL -filename "$filename" -filepath "$filepath" -filesize "$filesize" -rowcount "$rowcount" -xferpath "$DirTransfer"
                      Copy-Item -path $item.FullName -destination $DirTransfer -force -erroraction "silentlycontinue"
                      Move-Item -path $item.FullName -destination $archive_path -force -erroraction "silentlycontinue"
                      #Write-Host "$filename   $filepath   $filesize    $rowcount   $xferpath"
  
                  }
              Else # if there is no match
                  {
                      Write-Host $item.FullName "does not have a mapping"
                      Move-Item -path $item.FullName -destination $xferfail_path -force
                      $filename = $item.FullName
                      $email_body = "$filename `r`n`r`n does not have a file transfer mapping setup"
                      Send-MailMessage -To $email_recip `
                                       -From $email_from `
                                       -SmtpServer $smtp_server `
                                       -Subject "File Transfer Error - $item" `
                                       -Body $email_body `
                                       -Priority "High" `
                                       -Credential $smtp_cred
                  }
          }
  
  
  
  }
  ## IF NO FILES, THEN CLOSE
  Else
  {
      Write-Host "No File(s) Found - Aborting."
      Start-Sleep -s 5
  }
  
  ## SEND EMAIL NOTIFICATION IF SCRIPT ERROR
  
  If ($error.count -gt 0)
      {
          $email_body = "$error"
          Send-MailMessage -To $email_recip `
                           -From $email_from `
                           -SmtpServer $smtp_server `
                           -Subject "File Transfer Error - Script" `
                           -Body $email_body `
                           -Priority "High" `
                           -Credential $smtp_cred
      }
  </code></pre>