VSTS build doesn't pickup the dacpac file (hosted agent in cloud)

Question

I'm trying to use VSTS to deploy into my database, the problem is in one of the steps I need to pick up the dacpac file and deploy it to the Azure SQL server but it fails: in that step, I'm using "Execute Azure SQL: DacpacTask" which is provided by Microsoft in VSTS. there is a filed to do it which is called "DACPAC File" and the documentation said to use it like this:

$(agent.releaseDirectory)\AdventureWorksLT.dacpac

but it gave me the below error:

No files were found to deploy with search pattern d:\a\1\s\$(agent.releaseDirectory)\AdventureWorksLT.dacpac

so I did a cheating and put the below value in it:

d:\a\1\s\AdventureWorksLT.dacpac

it does work but obviously, it won't work forever as I need to use an environment variable, something like :

$(agent.releaseDirectory)\AdventureWorksLT.dacpac

any suggestion?

Answer 1

I've had this same problem. I wasn't able to find detailed documentation, but from experimenting, this is what I found.

I'm assuming that your DACPAC is created as part of a Build Solution task. After the build completes and the DACPAC is created, it exists in a sub-folder of the $(System.DefaultWorkingDirectory) directory.

Apparently, the Azure SQL Database Deployment task cannot access the $(System.DefaultWorkingDirectory) folder. So the file must be copied somewhere where it can be accessed. So here's what I did:

1. The Visual Studio Build task builds the solution, including the DACPAC. The resulting DACPAC is placed in a $(System.DefaultWorkingDirectory) sub-folder.
2. Add a Copy Files task as your next step. The Source Folder property should be "$(System.DefaultWorkingDirectory)". The Contents property should be "**/YourDacPacFilename.dacpac". The Target folder should be $(build.artifactstagingdirectory). The "**/" tells VSTS to search all subfolders for matching file(s).
3. Add an Azure SQL Database Deployment task to deploy the actual DACPAC. The DACPAC file will be in the $(build.artifactstagingdirectory).