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.
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.
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).
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.
/p:ExcludeObjectTypes), so you can get it to filter to what you want by excluding everything you don't want. You can do more targeted/convenient stuff by using the programmatic model exposed through the DacFx API, but that requires writing code.