Aggregate SQL output in file in Azure Devops piepline

_{Photo by Sergei Solovev on Unsplash}

In order to generate SQL statements based on a reference environment, we had to run multiples stored procedures, and aggregate results in a SQL file.

In order to automate this in a pipeline using Powershell Core Task, i saw this Example on how to manage output of Invoke-SQLCmd.

Then with this command i can append content to a file.

$sqlQuery=@"YOUR_SQL_STATEMENT_MULTILINE"@

$TableResult = Invoke-Sqlcmd -ServerInstance  "$(Server)" -Database "$(Db)" -Username "$(UserLogin)" -Password "$(userPassword)" -OutputAs DataTable -EncryptConnection  -Query $sqlQuery

$TableResult[0].Rows | %{Add-Content -Path .\Output.sql $_[0]}

Hope this helps !