Missing master.dacpac publishing dacpac Azure Devops

The problem

I have been trying to get a build pipeline to work in Azure DevOps to demo how we could build and test our databases.

I've come to the point where I want to publish my DACPAC but it gives me this error:

This throws me off because the master.dacpac is in the same directory as the dacpac that I'm trying to deploy.

Let's first explain how I set everything up.

I have two stages, build and test.

The build stage

The build runs on a hosted agent with this pool vmImage: vs2017-win2016
The build stage runs fine and in the end it copies all the files using the
CopyFiles task

- task: CopyFiles@2
    displayName: "Copy files to artifact directory"
    inputs:
      Contents: |
        **\$(database)-Tests\bin\$(configuration)\*.*
        **\*.publish.xml
      TargetFolder: '$(Build.ArtifactStagingDirectory)'
      FlattenFolders: true
      OverWrite: true

This results in a directory containing all the dacpac files and publish profiles.

The stage then publishes the artifact

- task: PublishBuildArtifacts@1
    displayName: 'Publish artifact'
    inputs:
      PathtoPublish: '$(Build.ArtifactStagingDirectory)'
      ArtifactName: $(artifactname)
      publishLocation: 'Container'

This all works as it's supposed to do.

The testing stage

During the testing stage I want to run a docker container that uses the latest SQL Server on Linux image.

The testing stage uses this pool vmImage: ubuntu-18.04

I set up these variables in the stage

variables:
    variables:
    dockerimage: 'mcr.microsoft.com/mssql/server:2017-latest'
    dockerpublishport: 1433
    dockername: sql1
    dockersqlpw: ''
    testresultpath: $(Build.Repository.LocalPath)/build
    dacpacfile: $(System.ArtifactsDirectory)/$(artifactname)/$(database)-Tests.dacpac
    publishfile: $(System.ArtifactsDirectory)/$(artifactname)/$(database)-Tests.publish.xml

The first step is to download the artifact that was created during the build stage

```

• task: DownloadB

This apparently was something very difficult or not many people have tried to achieve this yet.

The way I solved this issue was, instead of trying to get sqlpackage to work remotely with a the docker container, is to download the sqlpackage like before

- task: Bash@3
    displayName: 'Install sqlpackage'
    inputs:
      targetType: 'inline'
      script: |
        echo 'Creating sqlpackage dir'
        sudo mkdir $(Build.Repository.LocalPath)/sqlpackage

        # Install sqlpackage
        echo 'Downloading sqlpackage'
        sudo wget -q -O $(Build.Repository.LocalPath)/sqlpackage/sqlpackage.zip https://go.microsoft.com/fwlink/?linkid=2108814

        echo 'Extracting sqlpackage.zip'
        sudo unzip -qq $(Build.Repository.LocalPath)/sqlpackage/sqlpackage.zip -d /$(Build.Repository.LocalPath)/sqlpackage

        echo 'Changing sqlpackage permissions'
        sudo chmod +x $(Build.Repository.LocalPath)/sqlpackage/sqlpackage

and add the directory as a volume to the docker container

- task: Bash@3
    displayName: 'Start SQL Server Container'
    inputs:
      targetType: 'inline'
      script: |
        docker run --name $(dockername) \
          -e "ACCEPT_EULA=Y" \
          -e "SA_PASSWORD=$(dockersqlpw)" \
          -p $(dockerpublishport):1433 \
          --volume $(System.ArtifactsDirectory)/$(artifactname):/var/opt/cicd \
          --volume $(Build.Repository.LocalPath)/sqlpackage:/var/opt/sqlpackage \
          -d $(dockerimage)

I can then run docker exec

- task: Bash@3
    displayName: 'Publish DACPAC'
    inputs:
      targetType: 'inline'
      script: |
        echo 'Get files in directory'
        sudo docker exec $(dockername) bash -c 'ls -la $(dacpacpath)'

        sudo docker exec $(dockername) bash -c '/var/opt/sqlpackage/sqlpackage /a:Publish /tsn:localhost /tdn:$(database) /tu:sa /tp:"$(dockersqlpw)" /sf:$(dacpacfile) /pr:$(publishfile) /p:IncludeCompositeObjects=true'

This made sure that I would have both sqlpackage and the neccesary dacpacs inside the docker container. I could've done something similar with docker cp but this worked very well.

I'm sure that there is another viable solution out there to make this work in a more efficient way, but this was the easiest and fastest way to get this done.

If anyone has any pointers on how I could resolve this issue in another way, please let me know.

StackExchange Database Administrators Q#252556, answer score: 2