I am trying to automate existing sql server database deployment automation using Jenkins.
One of the ways to automate is to include the SQL scripts (stored procedures, views, table creation) in a SQL server Database project using Visual Studio. Use MSBuild to build the project and deploy dacpac using SQLPackage.exe. However, the existing database has reference to other databases inside Stored Procedures/Views so I have import other databases inside the SQL Server Database project. Additionally, I am getting a lot of errors when extracting dacpac of existing databases. I assume this is due to existing code is not dacpac compatible and may require changes to be dacpac compatible.
Is there any other way to automate SQL server code deployment instead of using SQL Server database project?
An alternative to SSDT model-based projects is a migration script approach as discussed here.
Model based deployments need to validate dependencies during build, else you would run the risk of errors during deployment. Unfortunately, I know of no easy way to create a dacpac for other referenced databases that aren't dacpac-friendly without going through the effort of creating separate projects for referenced databases built from source. You could create a database project for referenced databases and use the SSDT import wizard to extract database the schema for the project source code and clean up the solution.
The complexity of this task will vary considerably depending on the number of cross-database and cross-server dependencies you have. It may be necessary to split databases into separate projects in order to avoid circular references.