1

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.

Raj
  • 462
  • 3
  • 15
  • Yes and no. No, you can't ask it to include only particular object types. Yes, you can ask it to exclude particular object types ([`/p:ExcludeObjectTypes`](https://learn.microsoft.com/sql/tools/sqlpackage)), 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. – Jeroen Mostert Jul 17 '19 at 10:16
  • @JeroenMostert - Thanks for your input. It helped. I was looking for some property which gives option to include what I want to compare but it is opposite . Appreciate your help! – Raj Jul 18 '19 at 07:08

3 Answers3

1

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.

Ed Elliott
  • 6,666
  • 17
  • 32
  • It doesn't work for me ! I was testing by adding this property and i should not get any changes.Some how its not working.I still get View changes in deployment script "/p:ExcludeObjectTypes="Views, ServerRoles, ServerTriggers" – Raj Jul 19 '19 at 09:31
1

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).

jmurgic
  • 61
  • 4
0

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

Harsh Gupta
  • 307
  • 2
  • 14