CODEWITHSUNDEEP
sqlsql-serversql-server-data-toolsssdt-bi
Raj
Raj

Reputation: 502

Include only SP,Views,Table & Functions while deploying dacpac using sqlPackage.exe

We are working towards to automate SQL project deployment. When I do schema compare using Visual studio I have an option "application scoped" where I select only to compare SP,Table,Views & functions.

Now we are moving towards CI/CD and wanted to check if there is any way to restrict sqlPackage.exe to compare only listed type of objects and deploy changes only to those objects.

Upvotes: 1

Views: 2903

Answers (3)

Harsh Gupta
Harsh Gupta

Reputation: 327

I have multiple databases for a SAAS solution. Each tenant has its own database. I have configured the Migration project to migrate multiple databases using the DevOps pipeline. I used the DacDeployOptions and set the variable for ExcludeObjectTypes (as shown in the below screenshot). It works fine for me and ignores the table changes.

enter image description here

Upvotes: 0

jmurgic
jmurgic

Reputation: 61

You can use sqlPackage.exe to restrict the changes by specifying the types you don't want to deploy with parameter /p:ExcludeObjectTypes.

An example would be to use: /p:ExcludeObjectTypes="StoredProcedures;ScalarValuedFunctions;TableValuedFunctions"

Here is the list of possible arguments for the ExcludeObjectTypes parameter: https://learn.microsoft.com/en-us/dotnet/api/microsoft.sqlserver.dac.objecttype?view=sql-dacfx-150

Please not that ';' is used as a separator if you use ',' it will not work (and won't cause an error).

Upvotes: 1

Ed Elliott
Ed Elliott

Reputation: 6856

Yep two options, first exclude everything you don’t need /p:ExcludeObjectTypes - a csv list of everything apart from the things you are interested in:

https://learn.microsoft.com/en-us/sql/tools/sqlpackage?view=sql-server-2017

Or you can just not put them in your project and use the “don’t drop objects not in source dacpac”.

Use whatever you do in the short term with the goal of changing to having everything in your project over time.

Upvotes: 1

Related Questions