Location of SqlPackage.exe on VSTS and Azure

If you're looking to deploy a Visual Studio Database Project via VSTS to Azure, you can use the Azure SQL Database Deployment Release Task to include the deployment in your CI/CD process.

When a .dbproj builds, it creates a .dacpac file that contains the database definition/schema. The DB Deployment Release Task uses the SqlPackage.exe executable to deploy/sync the target database with the definitions in the dacpac file.

SqlPackage.exe can do more than just `publish`. However, at this time, the publish action is the only action supported by that release task.

If you'd like to perform other actions (e.g. `DeployReport` or `Script`), you can call the SqlPackage.exe directly using a PowerShell Script Release Task. However, to do that, you'll need to know the location of the SqlPackage.exe on the VSTS host, because it is not available in the PATH by default.

To find the location of SqlPackage.exe, the Azure SQL DB Deploy task uses this utility script: https://github.com/Microsoft/vsts-tasks/blob/master/Tasks/SqlAzureDacpacDeployment/FindSqlPackagePath.ps1

If you're using the `inline script` option of the PowerShell Script Release Task, you are limited to 500 characters (... yeah, I know) so you won't be able to use that method.

The method below is fragile, but to find SqlPackage.exe you can first create a temporary inline script that searches for SqlPackage.exe, then just hard code that path into your script.

Create an PowerShell script task with the following inline script:

dir -Path "c:\Program Files (x86)\Microsoft*" -Filter "SqlPackage.exe" -Recurse -ErrorAction SilentlyContinue | %{$_.FullName}

For me, that found the following instances of SqlPackage.exe (note: for some reason, the above `dir` command will take ~15 minutes to complete on VSTS. This was on the "Hosted VS2017" VSTS agent):

C:\Program Files (x86)\Microsoft Visual Studio\2017\Enterprise\Common7\IDE\Extensions\Microsoft\SQLDB\DAC\130\sqlpackage.exe
C:\Program Files (x86)\Microsoft Visual Studio\2017\Enterprise\Common7\IDE\Extensions\Microsoft\SQLDB\DAC\140\sqlpackage.exe

The SqlPackage.exe will usually be found under:

C:\Program Files (x86)\Microsoft Visual Studio
C:\Program Files (x86)\Microsoft SQL Server

In Azure, via the portal, if you open a console for a Web App, and execute the following commands, you may find SqlPackage.exe here:

cd d:\Program Files (x86)\Microsoft SQL Server\
dir /s /b *SqlPackage.exe 
D:\Program Files (x86)\Microsoft SQL Server\140\DAC\bin\SqlPackage.exe

Hope this helps!


Popular posts from this blog

Search iPhone Text Messages with SQLite SQL Query

Use Azure Functions to Execute a SQL Azure Stored Procedure on a Timer

How to Turn Off Microsoft Arc Touch Mouse Scroll Sound Vibration