AzureDevOps pipeline and sqlpackage

Question

All,

I would like to create a DACPAC file containing the Schema of the target database during the CI process. I can use this as a baseline schema to create a Testbed against which I can test the scripts in the release.

SqlPackage.exe /TargetFile:"C:\AzureDevOps\AdventureWorks.dacpac" /Action:Extract /SourceServerName:"SQL2016" /SourceDatabaseName:"AdventureWorks2016"

SqlPackage.exe /SourceFile:"C:\AzureDevOps\AdventureWorks.dacpac" /Action:Publish /TargetServerName:"SQL2016" /TargetDatabaseName:"AdWorks"

I can use the above scripts to create the DACPAC and then deploy the DACPAC to create the testbed.

I don't know how to incorporate this step in the AzureDevOps pipeline. I searched for the sqlpackage task but I could not find it.

Perform SQL server actions in Azure Pipelines or TFS

Can others throw some light on how to accomplish this?

Answer 1

SqlPackage executable needs to be installed in the Agent Machine inorder to execute the SqlPackage command. I'm not sure that HostedAgent having this Executable. So I'd recommend by creating our own Agent, where the Sqlpackage is installed. Then you can execute the scripts in AzureDevOps using your own Agent.

If you are using Azure SQL Database there is a default task available, where you can easily deploy the DACPAC file