Azure-pipelines-tasks: Azure SQL Database Deployment - Print outputs not shown, even when set to verbose

[timmkrause]

Environment

• Server - Azure Pipelines
• Agent - Hosted VS2017

Issue Description

• Task name - Azure SQL Database Deployment

Basically I want PRINT outputs from Invoke-Sqlcmd (which is used in case of "Inline SQL Script" and "SQL Script File") to show up in the log when -Verbose is provided.

Setting system.debug to True is not a real option.

Task logs

Nothing shows up between Invoke-Sqlcmd and Finishing but many PRINT outputs should be shown:

2019-02-04T08:23:05.7040752Z ##[section]Starting: Execute pre-deployment script
2019-02-04T08:23:05.7047737Z ==============================================================================
2019-02-04T08:23:05.7047887Z Task : Azure SQL Database Deployment
2019-02-04T08:23:05.7047949Z Description : Deploy Azure SQL DB using DACPAC or run scripts using SQLCMD
2019-02-04T08:23:05.7048046Z Version : 1.2.9
2019-02-04T08:23:05.7048095Z Author : Microsoft Corporation
2019-02-04T08:23:05.7048153Z Help : More Information
2019-02-04T08:23:05.7048263Z ==============================================================================
2019-02-04T08:23:14.4480132Z Sql file: D:a\r1a\Foo\Database\PreDeployment.sql
2019-02-04T08:23:14.4481060Z Invoke-Sqlcmd -ServerInstance "foo.database.windows.net" -Database "Foo-Pre-Production" -Username "foo@bar" -Password ** -Inputfile "D:a\r1a\Foo\Database\PreDeployment.sql" -Verbose -ConnectionTimeout 120
2019-02-04T08:23:19.2047193Z ##[section]Finishing: Execute pre-deployment script

[Ajay-MS]

@timmkrause

In order to get debug logs (output of PRINT statement) in SQL script using SQL Azure Dacpac deployment task, it includes two steps:

1. Enabled release debug logs ( Add release variable system.debug with value true)
2. In additional arguments of task, provide -verbose.

E.g.
Enable release debug logs

Provide -verbose argument

Task output

[timmkrause]

As I just mentioned "setting system.debug to True is not a real option".
IMHO this should only be used in debugging scenarios because it makes the whole pipeline noisy.

What I want is fine-grained control over certain tasks. In these 2 scenarios:

1. When deploying a DACPAC I want to see the actual change script, I achieved this via /Diagnostics:True (Azure SQL Database Deployment task uses sqlpackage.exe) - This is working totally fine (without enabling system.debug), sqlpackage forwards the additional output produced by /Diagnostics:True and I am able to see (besides other stuff) the change script.

2. When executing a script file (or an inline script) Invoke-Sqlcmd is used to achieve this task, my assumption was if I apply -Verbose to the Azure SQL Database Deployment task I am able to produce the same behavior as with sqlpackage and /Diagnostics:True which means that Invoke-Sqlcmd -Verbose logs should just be shown by the task. But this is not the case.

At a minimum this is an inconsistent behavior if not to say a bug?

We will pretty likely move into the direction of calling Invoke-Sqlcmd -Verbose directly so that there is no instance in between which swallows these logs.

[Ajay-MS]

Agree with you over inconsistent behaviour for verbose logs across Dacpac deployment and Script execution.

We will surely look into this.

[timmkrause]

Sounds great, thank you!

[lukaskolafa]

I am having the same issue. Thanks for opening and solving this ticket!

[timmkrause]

Why?

[Ajay-MS]

PR - https://github.com/Microsoft/azure-pipelines-tasks/pull/9741