Snippet Name: Skip_Processed_Files_Log

Tags:

Created Date: 2025-06-16 13:12:46
Last Modified Date: 2025-05-31 10:32:05

Dependencies:

DBA Notes:

Sample Output:

Title: Skip Processed Files Log
Author: user
In PowerShell, I have a Loop that is going thru an array of filenames with code below.
my issue is sometimes the loop will report an error for various reasons.

what i would like to do is if the process was successful, to store the string $UpgradeFile in a file, and read that into an array if i restart the process, and add logic to skip that file if it was found in the array
foreach($UpgradeFile in $AllCollections) # $UpgradeFile = $AllCollections[0]
{
  $FinalFileName = [System.IO.Path]::Combine($BaseDirectory,$UpgradeFile) #
  $StepTimeStart = Get-Date
  Write-Host "Starting $UpgradeFile at  $StepTimeStart" -foregroundcolor green
  $stepinfo = $null
  $Error.Clear()
  try
  { 
    if ($UpgradeFile.EndsWith('.sql'))
    {
      # Invoke-SqlCmd -InputFile $FinalFileName  -ServerInstance $ServerName -Database $DatabaseName -Querytimeout 0 -TrustServerCertificate -ApplicationName "PowerShell BPMSUpGrade" 
      Invoke-SqlCmd -InputFile $FinalFileName  -ServerInstance $ServerName -Database $DatabaseName -Querytimeout 0  -ApplicationName "PowerShell BPMSUpGrade"
    }
    elseif ($UpgradeFile.EndsWith('.ps1'))
    {
        Write-Host "PowerShell Script?" -ForegroundColor Yellow
    }
    
    elseif ($UpgradeFile.EndsWith('.custom'))
    {
        CustomCDCFortblInstanceActivities $ServerName $DatabaseName

    }
    elseif ($UpgradeFile.EndsWith('.custom2'))
    {
        CustomIndexesFortblInstanceActivities $ServerName $DatabaseName

    }
    elseif ($UpgradeFile.EndsWith('.custom3'))
    {
        CustomRenametblInstanceActivities $ServerName $DatabaseName

    }
        elseif ($UpgradeFile.EndsWith('.custom3'))
    {

        # Perform garbage collection on session resources 
        [System.GC]::Collect()         
        [GC]::Collect()
        [GC]::WaitForPendingFinalizers()
    }
    
    elseif ($UpgradeFile.EndsWith('.dacpac'))
    {
      Install-CoraSeQuenceDatabase -DatabasePackagePath $FinalFileName -SQLServer $ServerName -Database $DatabaseName -InstallType 'Upgrade' -InformationVariable stepinfo -OutVariable stepoutput 
      ##Publish-DbaDacPackage -SqlInstance $ServerName -Database $DatabaseName -Path $FinalFileName
    }

  }
  catch
  {
    $e = $MyInvocation.MyCommand.Name + "|" + ((Get-Date).ToString("yyyy-MM-dd HH:mm:ss")) + "|`n"
    for($i = $Error.Count -1 ;$i -ge 0;$i--) {$e = $e + "    " + $Error[$i].ToString() + "|`n" }
    Write-Host $e -ForegroundColor Red ##get out of the loop, if we had an error. don't keep trying to upgrade.
     if ($stepinfo -ne $null -and $stepinfo -is [array] )
     {
    if (!( $stepinfo[0].ToString() -eq "Deployment completed successfully"))
    {break}
  }
  }

Author: ChatGPT
Here's how you can modify your PowerShell script to track successful file executions and skip already processed files when restarting the script:

### **Approach**
1. **Maintain a Log File:** Store successfully processed filenames in a log file (`ProcessedFiles.txt`).
2. **Read the Log File at the Start:** Load it into an array and skip processing if a filename exists in the array.
3. **Append to the Log File:** If the file executes successfully, append it to `ProcessedFiles.txt`.

---

### **Modified Script**
```powershell
# Define log file path
$ProcessedLogFile = "C:\ProcessedFiles.txt"

# Read processed files into an array (if file exists)
$ProcessedFiles = @()
if (Test-Path $ProcessedLogFile) {
    $ProcessedFiles = Get-Content $ProcessedLogFile
}

foreach($UpgradeFile in $AllCollections) 
{
    # Check if the file was already processed
    if ($ProcessedFiles -contains $UpgradeFile) {
        Write-Host "Skipping previously processed file: $UpgradeFile" -ForegroundColor Yellow
        continue
    }

    $FinalFileName = [System.IO.Path]::Combine($BaseDirectory, $UpgradeFile)
    $StepTimeStart = Get-Date
    Write-Host "Starting $UpgradeFile at  $StepTimeStart" -ForegroundColor Green

    $stepinfo = $null
    $Error.Clear()

    try {
        if ($UpgradeFile.EndsWith('.sql')) {
            Invoke-SqlCmd -InputFile $FinalFileName -ServerInstance $ServerName -Database $DatabaseName -Querytimeout 0 -ApplicationName "PowerShell BPMSUpGrade"
        }
        elseif ($UpgradeFile.EndsWith('.ps1')) {
            Write-Host "PowerShell Script?" -ForegroundColor Yellow
        }
        elseif ($UpgradeFile.EndsWith('.custom')) {
            CustomCDCFortblInstanceActivities $ServerName $DatabaseName
        }
        elseif ($UpgradeFile.EndsWith('.custom2')) {
            CustomIndexesFortblInstanceActivities $ServerName $DatabaseName
        }
        elseif ($UpgradeFile.EndsWith('.custom3')) {
            CustomRenametblInstanceActivities $ServerName $DatabaseName
        }
        elseif ($UpgradeFile.EndsWith('.dacpac')) {
            Install-CoraSeQuenceDatabase -DatabasePackagePath $FinalFileName -SQLServer $ServerName -Database $DatabaseName -InstallType 'Upgrade' -InformationVariable stepinfo -OutVariable stepoutput
        }

        # If execution reaches here without errors, mark file as processed
        Add-Content -Path $ProcessedLogFile -Value $UpgradeFile
        Write-Host "Successfully processed: $UpgradeFile" -ForegroundColor Cyan
    }
    catch {
        $e = $MyInvocation.MyCommand.Name + "|" + ((Get-Date).ToString("yyyy-MM-dd HH:mm:ss")) + "|`n"
        for($i = $Error.Count -1 ;$i -ge 0;$i--) {
            $e = $e + "    " + $Error[$i].ToString() + "|`n"
        }
        Write-Host $e -ForegroundColor Red 

        if ($stepinfo -ne $null -and $stepinfo -is [array] ) {
            if (!( $stepinfo[0].ToString() -eq "Deployment completed successfully")) {
                break
            }
        }
    }
}
```

---

### **How It Works**
1. **Reads the `ProcessedFiles.txt` at the beginning** to know which files have already been executed.
2. **Before processing a file, it checks if it is in the list**:
   - If found ??? **Skips it**.
   - If not found ??? **Proceeds with execution**.
3. **If execution is successful, it appends the filename to `ProcessedFiles.txt`**.
4. **If an error occurs, the filename is NOT written to the log**, ensuring failed files are retried on the next run.

---

### **Benefits**
??? Prevents reprocessing of already successful files.  
??? Allows restarting from failures without manual intervention.  
??? Keeps track of progress in a simple text file.  
??? Works even if the script is interrupted.  

Would this fit your use case?

Author: user
i think this is exactly what i was looking for.

Author: ChatGPT
Great! This should make your process much more resilient and efficient. Now, if the script fails partway, it will automatically pick up where it left off without reprocessing successful files.

If you ever need enhancements, such as:
- **Storing logs with timestamps**
- **Retry logic for failed files**
- **Using a database or structured log instead of a text file**

Let me know! Happy scripting! ????