Powershell Try/Catch or If/Else is not working for my script

Question

I have the below powershell script which runs from my Jenkins:

$sqlpackage = Start-Process -FilePath sqlpackage.exe -ArgumentList '/Action:Publish','/SourceFile:"Database Services\bin\Release\Database Services.dacpac"',"/TargetConnectionString:""Data Source=${Env};Integrated Security=True;Persist Security Info=False;Pooling=False;MultipleActiveResultSets=False;Connect Timeout=60;Encrypt=False;TrustServerCertificate=False;Initial catalog=${Target}""",'/p:BlockOnPossibleDataLoss=True' -wait -PassThru -Credential $Cred -RedirectStandardOutput sqlstdout.txt -RedirectStandardError sqlstderr.txt
$sqlpackage.WaitForExit()
$sqlpackage

I tried to use the below options for Try/Catch or If/Else, but so far nothing is working (it goes to the Else / Catch even thought the script passed successfully:

OPTION 1

$sqlpackage = Start-Process -FilePath sqlpackage.exe -ArgumentList '/Action:Publish','/SourceFile:"Database Services\bin\Release\Database Services.dacpac"',"/TargetConnectionString:""Data Source=${Env};Integrated Security=True;Persist Security Info=False;Pooling=False;MultipleActiveResultSets=False;Connect Timeout=60;Encrypt=False;TrustServerCertificate=False;Initial catalog=${Target}""",'/p:BlockOnPossibleDataLoss=True' -wait -PassThru -Credential $Cred -RedirectStandardOutput sqlstdout.txt -RedirectStandardError sqlstderr.txt
$sqlpackage.WaitForExit()
$sqlpackage
if ($sqlpackage.LastExitCode -eq 0) {
    Get-Content sqlstdout.txt
}
else {
     echo "An error occurred $Error[0]"
     Get-Content sqlstderr.txt
     exit $sqlpackage.LastExitCode
}

OPTION 2

try
{
    $sqlpackage = Start-Process -FilePath sqlpackage.exe -ArgumentList '/Action:Publish','/SourceFile:"Database Services\bin\Release\Database Services.dacpac"',"/TargetConnectionString:""Data Source=${Env};Integrated Security=True;Persist Security Info=False;Pooling=False;MultipleActiveResultSets=False;Connect Timeout=60;Encrypt=False;TrustServerCertificate=False;Initial catalog=${Target}""",'/p:BlockOnPossibleDataLoss=True' -wait -PassThru -Credential $Cred -RedirectStandardOutput sqlstdout.txt -RedirectStandardError sqlstderr.txt
    $sqlpackage.WaitForExit()
    $sqlpackage
}
catch{
    echo "An error occurred $Error[0]"
    Get-Content sqlstderr.txt
    $LastExitCode = 1
    exit $LastExitCode 
}

Can you help me with the correct syntax? eventually the expected behavior is to show sqlstdout.txt in case it passed, and sqlstderr.txt in case it failed + fail the jenkins build.

Answer 1

Hi,

For the OPTION 1, the property in the condition of the if block should be "ExitCode", not "LastExitCode ".
For the OPTION 2, have you checked $Error[0]?

Best Regards,
Ian Xue

============================================

If the Answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

In Option 2 the Start-Process needs a "-ErrorAction STOP". Without that the script will continue if there's an exception. If that happens then line #4 may be throwing an exception because $sqlpackage might be null.