sqlpackage.exe publish fails with no error

Question

I have the below powershell script which runs from jenkins against windows server 2019 slave:

$sqlpackagepublish = 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=${Data_loss}" -wait -PassThru -Credential $Cred -RedirectStandardOutput sqlstdout.txt -RedirectStandardError sqlstderr.txt
$sqlpackagepublish.WaitForExit()
$sqlpackagepublish

if ($sqlpackagepublish.ExitCode -eq 0) {
     Get-Content sqlstdout.txt
 }
else {
     echo "An error occurred"
     Get-Content sqlstderr.txt
     exit $sqlpackagepublish.ExitCode
}

But the deploy fails with no error in sqlstderr.txt and no info in jenkins log. any idea how I can debug it?

Update

based on the suggested answer below, I've tried both approaches:

1.

Remove a -PassThru parameter and read files' content. So I changed my code the the below:

$sqlpackagepublish = 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=${Data_loss}" -wait -Credential $Cred
$sqlpackagepublish.WaitForExit()
$sqlpackagepublish

But now I'm getting:

You cannot call a method on a null-valued expression.
+ $sqlpackagepublish.WaitForExit()
+ ~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidOperation: (:) [], RuntimeException
    + FullyQualifiedErrorId : InvokeMethodOnNull

robust .NET'y way

In my original question, I had these lines:

$Username = $args[0]
$Password = $args[1]
$pass = ConvertTo-SecureString -AsPlainText $Password -Force
$Cred = New-Object System.Management.Automation.PSCredential -ArgumentList $Username,$pass
$sqlpackagepublish = Start-Process -FilePath sqlpackage.exe -ArgumentList {args} -wait -PassThru -Credential $Cred

I didn't understand how to add it to your code

Answer 1

This is how Start-Process command was basically created. -PassThru switch redirects the output to an object ($sqlpackagepublish in this case). More on Start-Process here: https://learn.microsoft.com/en-us/powershell/module/microsoft.powershell.management/start-process?view=powershell-5.1

There are few solutions.

1. Remove a -PassThru parameter and read files' content as you are doing it right now
2. Do it harder, but more robust .NET'y way:

function Start-ProcessExecution
{
    param (
        [string] $exe,
        [string] $arguments,
        [string] $user,
        [string] $password
    )
    $startInfo = New-Object System.Diagnostics.ProcessStartInfo;

    $pass = ConvertTo-SecureString -AsPlainText $password -Force
    $startInfo.UserName = "$user";
    $startInfo.Password = "$pass";

    $startInfo.FileName = $exe;
    $startInfo.Arguments = $arguments;
    $startInfo.UseShellExecute = $false;
    $startInfo.RedirectStandardOutput = $true;
    $startInfo.RedirectStandardError = $true;

    $process = New-Object System.Diagnostics.Process;
    $process.StartInfo = $startInfo;

    $process.Start() | Out-Null;

    $output = $process.StandardOutput.ReadToEnd();
    $err = $process.StandardError.ReadToEnd();

    $process.WaitForExit();

    $obj = [PSCustomObject]@{
        ExitCode = $process.ExitCode
        StdOut = $output
        StdErr = $err
    }
    return $obj;
}

$exe = "sqlpackage.exe"
$arguments = [string]::Join(" ", "/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=${Data_loss}')

$result = (Start-ProcessExecution -exe $exe -arguments $arguments -user $args[0] -password $args[1])
$ec = $result.ExitCode;

if ($ec -eq 0) {
    Write-Host "STDOUT:`n$($result.StdOut)";
}
else {
    Write-Host "STDERR:`n$($result.StdErr)";
}

Wrapper is basically there to better readability and is constructed to prevent the deadlock on reading huge outputs after the WaitForExit() has been called.