Powershell - why is exception treated differently depending on stderr redirection/how to access "multiple" exceptions in a catch?

Question

This question is related to the question here:

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

However I have a problem with the workaround that I'm developing, shown below

    try {
    & "C:\Program Files (x86)\Microsoft SQL Server\110\DAC\bin\SqlPackage.exe" /SourceFile:"$dacpac" /Profile:"$dbProfile" /p:UnmodifiableObjectWarnings=false /Action:Publish 2>&1
}
catch {
    Write-Host "Exception: $_"
    if($_.TargetObject -like $Pattern) {
        Write-Host "Known exception - treat as warning"
    }
    if($LastExitCode -ne 0) {
        throw "An error occurred deploying database. Please examine the log to determine the nature of the error"
    }
}

If I don't redirect stderr to stdout, when sqlpackage logs a warning, the process indicates a failure.

If I redirect stderr to stdout in the previous command, when the sqlpackage.exe throws a warning the process is able to complete, but once it has completed an exception is thrown. I can then examine the exception text, which will be the same as my known exception, and I can safely ignore it.

However something as well as the warning is logged by sqlpackage - i.e. an actual failure error, I'm unable to determine the additional text of the exception. Inside the catch, all I can see is the warning message. If I remove the stderr redirection, the full output is logged, which is an error relating to permissions.

Therefore, my script succeeds when it should, and ignores warnings that I don't care about it. It fails when I want it to as well, but I can't then output the appropriate log messages. I then need to edit my deployment script, rerun the deployment etc. etc., all fiddly, and not repeatable.

Any tips would be much appreciated.

Answer 1

This issue has been fixed in the latest SqlPackage.exe release - that's the June 2014 DacFramework.msi release, which is bundled into the July 2014 SQL Server tooling update for Visual Studio. Warnings are now directed to StdOut rather than StdErr.

Please note that as of April 2014 the location of SqlPackage.exe has changed - if installed via SSMS or the DacFramework.msi it is located in "C:\Program Files (x86)\Microsoft SQL Server\120\DAC\Bin". If installed as part of the SQL Server tooling in VS, it's located under "C:\Program Files (x86)\Microsoft Visual Studio \Common7\IDE\Extensions\Microsoft\SQLDB\DAC\120", where VisualStudioVersion is "11.0" for VS2012 and "12.0" for VS2013.