How to generate DACPAC file

Question

I'm trying to deploy my project in Azure DevOps through IIS website and SQL deployment. However I am struggling with deploying SQL part as I do not have a .dacpac file in my build artifacts. How do I generate this as any option that I have tried it ended up with failing of the process.

P.S. I do not have access to the VM where I am deploying due to restrictions. I can access database as I marked as DBO on the machine.

My question is also, do I need to generate this DACPAC file through build every time or it can be generated only once, stored on machine, and I point from deployment process to that file?

Thank you for your help!

Mccraw

I am having a project with db-up so cant generate dacpac. Any help after this input? Thanks – Mccraw

Answer 1

However I am struggling with deploying SQL part as I do not have a .dacpac file in my build artifacts. How do I generate this as any option that I have tried it ended up with failing of the process. I can access database as I marked as DBO on the machine.

1. Firstly you have to create SQL Server Database Project using SSDT (or Azure Data Studio insiders preview) by importing objects of the live database.

2. The database project then is to be placed into a repository

3. The pipeline (classic or yaml) is to have a build task MSBuild@1. Here is an YAML example. It generates the dacpac

- task: MSBuild@1
    displayName: 'Build solution YourDatabase.sln'
    inputs:
      solution: 'src/YourDatabase.sln'

This task compiles the database project and produces dacpac file(s)

4. Then produced files are to be extracted:

- task: CopyFiles@2
    displayName: 'Extract DACPACs'
    inputs:
        CleanTargetFolder: false
        SourceFolder: '$(agent.builddirectory)\s\src\YourDatabase\bin\Debug\'
        Contents: '*.dacpac'
        TargetFolder: '$(build.artifactstagingdirectory)'

5. And finally, published as the artefact

  - task: PublishPipelineArtifact@1
    displayName: 'Publish Artifact'
    inputs:        
        targetPath: '$(build.artifactstagingdirectory)'
        artifact: 'drop'

6. Deployment of the dacpac is the final goal and can be done using SqlDacpacDeploymentOnMachineGroup@0, however, this is out of the scope of the original question

My question is also, do I need to generate this DACPAC file through build every time or it can be generated only once, stored on machine, and I point from deployment process to that file?

It depends.

• Classic pipelines have a separation of BUILD and RELEASE phases. In this case, you can build it once and reuse that dacpac for many future releases.

• In case of multi-stage yaml pipelines, it is common that every pipeline run triggers build and deployment stages, because they are still belong to the same pipeline and run as a single unit work.

Jacklighter

It is db Up it seems. I cant generate dacpac. How to proceed then? – Mccraw

@AhmedB, I think nobody can give a good answer why dacpac cannot be generated until you show us the code of your pipeline and what error message you received – Jacklighter

Could you possibly show what an entire yaml file might look like? I'm having trouble getting this right, and I'm sure it's because of my inexperience with proper yaml syntax in general but if I had a full example to work from I'm certain I could figure it out. – Gallego

Oh, I answered that one a few years ago, not sure if it is still actual. You can check my original blog post for more detailed example: alexvolok.com/2020/… – Jacklighter

Answer 2

I know this is an old thread but I could not find the dacpac in my copy files process.

Look in the logs of your MSBuild process and search for DACPAC. That will show you the path where it's created.

The Source Folder details should then be rooted at \S

For example:

MSBuild creates DACPAC in D:\a\8\s\SomeProject\Database\SomeProject\SomeProject\bin\Debug\

The Source Folder in the Copy Files task should look like this: $(agent.builddirectory)\s\SomeProject\Database\SomeProject\SomeProject\bin\Debug\

Hope this helps

Patino