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.

A `dacpac` *is* the script files, or rather, the definitions of all objects as script. `I don't want to create DB project using Visual Studio` why? `And I am not in a situation to pay for license.` what license? There are no licenses involved. A `dacpac` is the output of a Database Project. Both SSDT and SSMS are free standalone downloads. You can also use Azure Data Studio if you want to work on Linux or Mac. Neither of these requires any license — Panagiotis Kanavos, Feb 19 '21 at 10:56
What is your *actual* problem? Why such arbitrary restrictions, and why do you describe a Database project but say you don't want to use one? — Panagiotis Kanavos, Feb 19 '21 at 11:06
Are you trying to package dynamically created files? You can still use a `sqlproj` for this that [includes files using a wildcard](https://github.com/dotnet/sdk/issues/3026#issuecomment-475000025). You can build the package with `dotnet build` — Panagiotis Kanavos, Feb 19 '21 at 11:17
Hi friend, how about the issue? Does the answer below resolved your question? — Walter, Feb 25 '21 at 08:37

score 1 · Answer 1 · answered Feb 19 '21 at 10:52

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

answered Feb 19 '21 at 10:52

Shamrai Aleksander

13,096
3
24
31

score 0 · Answer 2 · answered Feb 19 '21 at 11:03

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

score 0 · Answer 3 · answered May 13 '23 at 02:02

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.

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

3 Answers3