Suppress sqlpackage.exe warnings/errors in Powershell when triggered using TFS build

Question

I have an automated deployment script to deploy database changes using a dacpac and sqlpackage.exe, SSRS reports and a website.

When I execute the script at the command line, sqlpackage outputs the following message:

The object [objectname] already exists in database with a different definition and will not be altered.

The output is in yellow on the console, indicating it is a warning, the deployment continues and completes as expected.

However when I trigger the same script through TFS build, the same message is logged in the Build explorer, but with the error icon beside it. The deployment still continues, automated tests are run etc., but the build status changes to the icon with a tick and cross in it - indicating the build completed successfully, but other tasks failed.

There is a parameter that can be provided to sqlpackage, UnmodifiableObjectWarnings, which is supposed to suppress this kind of warning, however there is a post on msdn indicating there is a bug with this parameter, and it doesn't work.

I'm wondering how else in Powershell I can trap and suppress this kind of warning, as I don't want my build/deployment to fail just because of this warning. However I can't suppress all error output from the call to sqlpackage, as there may be other, completely valid errors.

Update

I've tried following the suggestions here: https://serverfault.com/questions/340711/redirect-stderr-to-variable-in-powershell and have tried using similar to the following code:

  try {
        $test = & "C:\Program Files (x86)\Microsoft SQL Server\110\DAC\bin\SqlPackage.exe" /SourceFile:"$dacpac" /Profile:"$dbProfile" /p:UnmodifiableObjectWarnings=True /Action:Publish 2>&1

        $errMsg = $test | ?{$_.gettype().Name -eq "ErrorRecord"}
        $normMsg = $test | ?{$_.gettype().Name -ne "ErrorRecord"}
    }
    catch {
         Write-Host "Caught Exception $_"
    }

however as soon as the warning message is returned by the exe, an exception is thrown and handled by my calling function - and the deployment does not occur.

However if I call it as follows:

try {
    & "C:\Program Files (x86)\Microsoft SQL Server\110\DAC\bin\SqlPackage.exe" /SourceFile:"$dacpac" /Profile:"$dbProfile" /p:UnmodifiableObjectWarnings=True /Action:Publish 2>&1
}
catch {
     Write-Host "Caught Exception $_"
}

i.e. without the variable $test capturing output, the deployment completes successfully, but the catch still catches the same exception. Why does capturing the output in a variable cause the exception to be thrown immediately, however not capturing it still causes it to be caught, but after completing successfully?

Answer 1

I suggest using the latest version of SqlPackage. Looking at your path it looks like an older version. There was a bug a long time ago where warnings were written to stderr instead of stdout. if you upgrade the issue should go away, if you dont you can use powershell to start a new .net process where you then have the ability to redirect error output.