HOWTO: Filter Dacpac Deployments
I have changed the details about passing multiple filters at the bottom, see the codepex site for full details.
Download the filter from: https://agilesqlclub.codeplex.com/releases/view/610727 put the dll into the same folder as sqlpackage.exe and add these command line parameters to your deployment:
This will neither deploy, drop or alter anything in the BLAH schema.
I recently discovered deployment contributors which let you write .net code to interrupt the dacpac deployment process and modify either the steps involved or the actual scripts themselves. They are documented: http://msdn.microsoft.com/en-us/library/microsoft.sqlserver.dac.deployme...
Well, I cannot tell you how excited I was and indeed how excited I still am - very excited.
When you use the schema compare tool inside visual studio you can choose what you deploy and what you do not deploy but when you have a dacpac and try to deploy that outside of visual studio, there isn't a way to do it so I thought I would write a generic filter that can be used to filter what is deployed.
This also gave me a chance to test visual studio online with a real project and also codeplex as I was interested how it compares to github.
I have now deployed the code to codeplex and you can use it to see how the deployment contributors are written but they are very simple, the releases section also has the compiled code for anyone who doesn't want to use git clone:
How do I use this?
Ok so the way the DacFx api works is that you need to put the dll that contains the contributor into the same folder as sqlpackage.exe - this obviously isn't ideal if it is a shared machine so I would copy the DAC folder that contains sqlpackage.exe and move it somewhere else and copy the dll into that folder and run the deployment from there.
Once the dll is in the same folder as sqlpackage.exe you need to tell it to load the contributor which you do using this argument:
The next step is to decide which filters you would like to use.
There are two types of filters: Keep and Ignore.
Keep filters stop objects being dropped when they are in the dacpac but not the destination, if they are in the dacpac and not in the destination *or are different* then they will be created or altered.
Ignore filters stop any sort of operation, create, alter or drop so there is some flexibility.
Keep are really only ever used in combination with /p:DropObjectsInSource=True otherwise they wouldn't be dropped anyway.
Once you know what type of filter you want you need to decide what you will filter on, your choices are: Name, SchemaName and object type (stored procedure, function, table, user, role, rolemembership etc etc).
Name filters work on an objects name, pretty straight forward/
Schema filters work on the name of the schema so you can keep or ignore everything in a specific schema
Object type filters work on the type of the object as the Dac Fx api sees it, these types are all documented as properties of the ModelSchema class:
The object types are all fields, so the list starts Aggregate, ApplicationRole etc etc.
Once you have decided how you will filter you specify the filter itself which is a regex, but don't be scared it doesn't have to be complex.
Because of the way we pass the arguments to SqlPackage.exe and it then parses them and passes them onto the deployment contributor it is a little rigid, but essentially the filter itself look like:
To keep everything in dbo:
To ignore all Tables:
To keep a table called MyTable or MyExcellentFunnyTable:
When you have decided onthe filter you use need to pass it to SqlPackage.exe using:
You can specify multiple filters by seperating them with a semi colon so and adding a uniqeifier to the end of each arg name:
(The reason for the uniqueifier is detailed: https://connect.microsoft.com/SQLServer/feedback/details/1112969)
That should be it, it is fairly straightforward and if you get any issues then please shout either via email or twitter @EdDebug and I will help as quickly as I can, although you can do a git clone, fix and issues and send a pull request!