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.

_"in case it failed "_ What do you mean by 'failed'? `try`/`catch` will only work if an exception is thrown. If the command runs properly, but just doesn't do the thing you wanted, then it isn't much use. Similarly, with the `LASTEXITCODE` - if the process doesn't return non-zero codes, then that won't work either. You will need to find a reliable way to determine success/failure and work with that. — boxdog, Jun 08 '21 at 08:50
@boxdog I'm pretty sure the `Start-Process` returns `LASTEXITCODE` . @Scepticalist, where exactly? BTW, I've just found this answer, do you think it can help with my case? https://stackoverflow.com/a/4146198/11705021 — arielma, Jun 08 '21 at 09:02
_"do you think it can help with my case?"_ it's impossible to say, since it is not clear what is happening in your scenario. Is there an exception? What exit codes are returned? — boxdog, Jun 08 '21 at 09:13

score 1 · Accepted Answer · answered Jun 08 '21 at 10:14

1

For the OPTION 1, the property in the condition of the if block should be ExitCode, not LastExitCode.

answered Jun 08 '21 at 10:14

arielma

1,308
1
11
29

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

1 Answers1