Missing master.dacpac publishing dacpac Azure Devops

Question

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: '<YourStrong@Passw0rd>'
    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: DownloadBuildArtifacts@0
    displayName: 'Dowload Artifacts'
    inputs:
      buildType: 'current'
      downloadType: 'specific'
      downloadPath: '$(System.ArtifactsDirectory)'

Because you have to use sqlpackage to deploy the dacpac I also created a task to download that executable.

- 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 

I then start the docker run command

- task: Bash@3
    displayName: 'Start SQL Server Container'
    inputs:
      targetType: 'inline'
      script: 'docker run -e "ACCEPT_EULA=Y" -e "SA_PASSWORD=$(dockersqlpw)" -p $(dockerpublishport):1433 --name $(dockername) -d $(dockerimage)'

Up to here everything is running fine. The task that gives me trouble is the actual publish of the dacpac.

- task: Bash@3
    displayName: 'Publish DACPAC'
    inputs:
      targetType: 'inline'
      script: |
        echo 'Current Directory'
        pwd

        echo 'Changing directory to artifact dir'
        cd $(System.ArtifactsDirectory)/$(artifactname)

        echo 'Current Directory'
        pwd

        echo 'Get files in directory'
        ls -la

        echo '$(Build.Repository.LocalPath)/sqlpackage/sqlpackage /a:Publish /tsn:$(dockerip) /tdn:$(database) /tu:sa /tp:"$(dockersqlpw)" /sf:$(dacpacfile) /pr:$(publishfile) /p:IncludeCompositeObjects=true'
        sudo $(Build.Repository.LocalPath)/sqlpackage/sqlpackage /a:Publish /tsn:$(dockerip) /tdn:$(database) /tu:sa /tp:"$(dockersqlpw)" /sf:$(dacpacfile) /pr:$(publishfile) /p:IncludeCompositeObjects=true

The tasks executes the following:

1. return the current directory
2. change to artifact directory and show that I'm actually in it
3. show the files to be sure that they're all there
4. run the command to publish the dacpac

What have I already done to fix it

The first thing I checked was of course if the dacpac was there. That's where the debug message in the task is for to show all the files.

The next thing I did was to make sure that the project file did not have the path bug where it specifies the direct path but uses the $(dacpacroot) variable.

Besides those tests, I also did a whole bunch of testing with permissions, making sure that I ran the command with sudo and even tried putting the actual master.dacpac in my solutions to reference it locally but nothing worked.

I've come to the point that I have tried every little thing I could think of to make this work.

Does anybody know what I can do to fix this.

Answer 1

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.