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
Hope this helps!
Aaron
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
or
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!
Aaron
Comments