Capture errors within ForEach-Object -Parallel in Powershell 7

752 views Asked by At

I am trying to execute Powershell script (7.0) file using Powershell 7-Preview which iterates through all the databases, update them using DACPAC file and execute other SQL Server scripts in all the DBs. It works fine when there is no errors, however, in case of an error while executing Dacpac file It gives below error and stops executing the script further.

##[error] Could not deploy package. ##[error]PowerShell exited with code '1'.

Any pointer on how we can catch the errors gracefully in PowerShell within the Parallel statement and let script to be continued for other databases? Try-Catch block does not seem to be working here.

I am new to PowerShell. This PowerShell script is a part of DevOps release pipeline.

#.. Variables are defined here ..#
[string[]]$DatabaseNames = Invoke-Sqlcmd @GetDatabasesParams | select -expand name

[int]$ErrorCount = 0
$DatabaseNames | ForEach-Object -Parallel {
    try 
    {
        echo "$_ - Updating database using DACPAC."    
        dir -Path "C:\Program Files (x86)\Microsoft Visual Studio*" -Filter "SqlPackage.exe" -Recurse -ErrorAction SilentlyContinue | %{$_.FullName} {$using:SqlPackagePath /Action:Publish /tu:$using:DatabaseUsername /tp:$using:DatabasePassword /tsn:$using:ServerInstance /tdn:"$_" /sf:using:$DacpacLocation /p:BlockOnPossibleDataLoss=False}
     
        echo "$_ - Updating the scripts."
        $OngoingChangesScriptParams = @{
            "Database" = "$_"
            "ServerInstance" = "$ServerInstance"
            "Username" = "$DatabaseUsername"
            "Password" = "$DatabasePassword"
            "InputFile" ="$SystemWorkingDir\$OngoingChangesScriptLocation" 
            "OutputSqlErrors" = 1
            "QueryTimeout" = 9999
            "ConnectionTimeout" = 9999
        }

        Invoke-Sqlcmd @OngoingChangesScriptParams       
    }
    catch {
        $ErrorCount++
        echo "Internal Error. The remaining databases will still be processed."
        echo $_.Exception|Format-List -force
    }
}```

Logs- 
2020-09-17T19:21:59.3602523Z *** The column [dbo].[FileJob].[GMTOffset] on table [dbo].[FileJob] must be added, but the column has no default value and does not allow NULL values. If the table contains data, the ALTER script will not work. To avoid this issue you must either: add a default value to the column, mark it as allowing NULL values, or enable the generation of smart-defaults as a deployment option.
2020-09-17T19:21:59.4253722Z Updating database (Start)
2020-09-17T19:21:59.4274293Z An error occurred while the batch was being executed.
2020-09-17T19:21:59.4280337Z Updating database (Failed) 
2020-09-17T19:21:59.4330894Z ##[error]*** Could not deploy package. 
2020-09-17T19:22:00.3399607Z ##[error]PowerShell exited with code '1'. 
2020-09-17T19:22:00.7303341Z ##[section]Finishing: Update All Companies
0

There are 0 answers