How to create DacPac from script files to automate DB deployment?

Question

I need to automate SQL Server DB deployment using Azure DevOps. I don't want to give any alter statement. I will have a folder structure with tables, stored procedures, views & functions in repository. Every folder will contain only create scripts. Is there anyway to create DacPac file with that folder structure or any other way, other than DacPac deployment using that folder structure?

Note: I don't want to create DB project using Visual Studio. And I don't want to create a DacPac file directly from SQL Server Management Studio and checkin the same to source control. And I am not in a situation to pay for license.

Answer 1

You can try to use SQL Database Projects extension in Azure Data Sudio. It supports VS SQL Database Project and it supports builds from the command line: Build a database project from command line

Answer 2

What you describe is the database project. A dacpac is the build output of a Database project. There are no licenses involved. All the tools that produce dacpac files are free:

• Both SQL Server Data Tools (SSDT) and SQL Server Management Studio (SSMS) are free, standalone downloads.
• You can install SSDT on top of Visual Studio Community which is also free.
• Azure Data Studio supports database projects. Also free, open source and works on Mac and Linux. I'm using it on Mac to edit database projects, using a SQL Server Developer Edition in a Docker container.
• The sqlpackage command-line tool can extract a dacpac from an existing database, publish it or generate a migration script. Also free and a standalone download

Answer 3

Not exactly about sql server or dacpac, but more generic solution applicable to this use case.

Please take a look at liquibase and flyway projects. Both can help to keep track of what’s already deployed to database and deploy any new items automatically as part of CICD pipeline. Flyway could be better solution to your question, mostly you need to add some prefix to existing file names defining order of deployment, flyway can manage rest of the tasks for you. Liquibase has its own advantages, but adopting it at this stage needs some changes across all scripts.

Both projects have info on how to include this step in your CICD pipeline.