question

arielman2304-0180 avatar image
0 Votes"
arielman2304-0180 asked RichMatheisen-8856 answered

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

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.





windows-server-powershell
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

IanXue-MSFT avatar image
0 Votes"
IanXue-MSFT answered arielman2304-0180 commented

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.

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Indeed option 1 is now working well after you suggestion :)

0 Votes 0 ·
RichMatheisen-8856 avatar image
0 Votes"
RichMatheisen-8856 answered

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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.