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

Karolina Ochlik · Accepted Answer · 2021-06-14T17:28:25.457

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.

Remove a -PassThru parameter and read files' content as you are doing it right now
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.

It looks better, but something with the `$arguments` is not working: `*** Argument 'BlockOnPossibleDataLoss' has an invalid value: '${Data_loss}'.` and after I removed it, the next error is `Unable to connect to target server '${Env}'`. anyway, for now I wrote it hard coded and got the real error, so I'm working on fixing it :) — arielma, Jun 14 '21 at 09:03
Tnx. one more thing, I must use also the `-Credential $Cred` like in my original question. can you edit your answer with that as well? — arielma, Jun 14 '21 at 09:43
Edited again :) You can also change the function to allow passing the credentials to it — Karolina Ochlik, Jun 14 '21 at 09:49
Ok, let's focus on your first suggestion :) I removed it, but now I get this error: `You cannot call a method on a null-valued expression. + $sqlpackagepublish.WaitForExit()` — arielma, Jun 14 '21 at 10:18
I didn't write in any place process object variable named `$sqlpackagepublish`. Please follow my code correctly :) — Karolina Ochlik, Jun 14 '21 at 10:22
Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/233746/discussion-between-arielma-and-grzegorz-ochlik). — arielma, Jun 14 '21 at 10:35
I updated my questions both suggestions + the current output. can you please have a look? — arielma, Jun 14 '21 at 12:50

sqlpackage.exe publish fails with no error

1 Answers1