HOWTO: Filter Dacpac Deployments

Updated 2015-02-02

I have changed the details about passing multiple filters at the bottom, see the codepex site for full details.

tldr;

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:

/p:AdditionalDeploymentContributors=AgileSqlClub.DeploymentFilterContributor /p:AdditionalDeploymentContributorArguments="SqlPackageFilter=IgnoreSchema(BLAH)"

This will neither deploy, drop or alter anything in the BLAH schema.

More Info

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:

https://agilesqlclub.codeplex.com/

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:

/p:AdditionalDeploymentContributors=AgileSqlClub.DeploymentFilterContributor

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:

http://msdn.microsoft.com/en-us/library/microsoft.sqlserver.dac.model.mo...

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:

KeepSchema(dbo)

To ignore all Tables:

IgnoreType(Table)

To keep a table called MyTable or MyExcellentFunnyTable:

KeepName(.*yTabl.*)

When you have decided onthe filter you use need to pass it to SqlPackage.exe using:

/p:AdditionalDeploymentContributorArguments="SqlPackageFilter=KeepSecurity"

You can specify multiple filters by seperating them with a semi colon so and adding a uniqeifier to the end of each arg name:

/p:AdditionalDeploymentContributorArguments="SqlPackageFilter0=KeepSecurity;SqlPackageFilter1=IgnoreSchema(dev)"

(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!

Enjoy!

Comments

This is great work, Ed. Cheers!

Thanks Steven, I hope someonefinds it useful!

Hi Ed. First of all, thanks for spending the time doing this. I do have a problem though. I have copied your dll to the same folder as where the sqlpackage.exe is but I get the following error: "Required contributor with id 'AgileSqlClub.DeploymentFilterContributor' could not be loaded". (I am using the Script action, not Publish, if that makes a difference). Cheers.

Hey Arno - probably the easiest thing is if you email me ed@agilesql.club.999 (remove the .999) and I will help you get it working!

Hi there. I got the following error when trying to use this dll: "Required contributor with id 'AgileSqlClub.DeploymentFilterContributor' could not be loaded".
It turns out this happens because of Windows security (I am using Win 8.1).
To fix, r-click on the dll and click the "Unblock" button. Please note that you would have to do this before you copy it to the sqlpackage.exe directory (C:\Program Files (x86)\Microsoft SQL Server\120\DAC\bin), otherwise Windows will not unblock the file.
Hope this saves someone some time.
Cheers

Great thanks for the feedback, I will add it to the main site!

I'm trying to get sqlpackage to ignore synonyms (using the Script action), but it still keeps scripting the dropping and creation of them.

I'm using:
/p:AdditionalDeploymentContributors=AgileSqlClub.DeploymentFilterContributor /p:AdditionalDeploymentContributorArguments="SqlPackageFilter=IgnoreType(SYNONYM)"

What and I doing wrong?

Hi Matthew,

The type is case sensitive so it should be Synonym and it will work:

SqlPackageFilter=IgnoreType(Synonym)

I will fix it so that type is no longer case sensitive but have quite a lot on so will take a while.

The correct casing is found in this msdn article:

https://msdn.microsoft.com/en-us/library/microsoft.sqlserver.dac.model.aspx

(Search for "Microsoft.SqlServer.Dac.Model Namespace" when they do the usual msdn links re-shuffle!)

ed

Is it possible to load this dll via c# source code?
Instead of using sqlpackage.exe, I'm trying to create my own app that refers to DacFx dlls along with AgileSqlClub.SqlPackageFilter.dll. my code looks like this:


var dacOptions = new DacDeployOptions();
dacOptions.AdditionalDeploymentContributors = "AgileSqlClub.DeploymentFilterContributor";
dacOptions.AdditionalDeploymentContributorArguments = "SqlPackageFilter=KeepName(myTable)";

and it cause runtime error during DacServices.Deploy() :

Contributor initialization error.
An error occurred during deployment plan generation. Deployment cannot continue.
Error SQL0: Required contributor with id 'AgileSqlClub.DeploymentFilterContributor' could not be loaded.

All referred dlls including AgileSqlClub.SqlPackageFilter.dll are all created within the same Debug/Release Directory as usual. but not working.
any suggestions?

Hi Anonymous (if that is who you really are),

Yes, you certainly can call it from C# but the DacFx is a little unclear as to where it looks for the contributor - the easiest thing to do is to run process monitor (http://live.sysinternals.com/procmon.exe), add a path filter for "contains" "AgileSqlClub.DeploymentFilterContributor" - see where it is looking and put it in one of those folders.

Ed

you should right click on dll and properties and Unblock and apply.

Awesome, thanks! This suggestion fixed it for me

I want to keep table named "_table1" . but still they are dropping. (DacPac include only Employees table)
console:
Updating database (Start)
Dropping [dbo].[_table1]...
Dropping [dbo].[_Table_4]...
Dropping [dbo].[Table_3]...
Update complete.
Updating database (Complete)
Successfully published database.

I use this filter :
sqlpackage.exe /Action:Publish /TargetServerName:localhost /TargetDatabaseName:SqlPackageFilter /SourceFile:DacPac.dacpac /p:DropObjectsNotInSource=true /p:AdditionalDeploymentContributorArguments="SqlPackageFilter= KeepName(_Table1)"

I want to NOT drop columns from selected tables if they exist in the target DB, but do not exist in the dacpac. Is this possible with your DLL? However I want it to continue adding new columns (for all tables, including the ones selected above) if they exist in the dacpac, but not the target DB.

At a guess you can probably do it if you say to keep the table itself :

"KeepName(TableName)"

I haven't tested it, if it doesn't work then reply here and I will take another look and add it if it is missing as I have heard other people asking for this before.

UPDATE: This has now been added as a new filter type, see: https://the.agilesql.club/blog/Ed-Elliott/2015/09/23/Deployment-Contribu...

Hi Ed,
Sometimes, I have a following error during publishing (in Updating database state):
"...
Dropping [SqlQueryNotificationService-6c638d1f-8c54-4b9f-bf06-08e735cf5b42]..."
.Net SqlClient Data Provider: Msg 15151, Level 16, State 1, Line 1 Cannot drop the service 'SqlQueryNotificationService-6c638d1f-8c54-4b9f-bf06-08e735cf5b42', because it does not exist or you do not have permission."
Script execution error. The executed script: _%nL%_ DROP SERVICE [SqlQueryNotificationService-6c638d1f-8c54-4b9f-bf06-08e735cf5b42]
An error occurred while the batch was being executed."
I thought that I can use a filtering mechanism to avoid dropping services.
I set following properties to my DacDeployOptions object:
AdditionalDeploymentContributors = "AgileSqlClub.DeploymentFilterContributor",
AdditionalDeploymentContributorArguments = "SqlPackageFilter=IgnoreType(Services)"
I the beginning, I had all problems, recently described here. Using your excellent explanations, I fixed each one of them.
Finally, when every thing looks good, I got my original error again!!!
Could you provide me with a help of this?

Hey,

You should be able to use the built in sqlpackage.exe command to stop it being dropped, did you try: "/p:DoNotDropObjectType=Services"?

If it doesn't work you could try filtering it by name instead of type "IgnoreName(SqlQueryNotificationService.*)"

Let me know if that works and I will check to see if the type of the service is something other than "Services".

Ed

The first thing that I tried was DoNotDropObjectType=Services". It did not work
Now I tried a following:
AdditionalDeploymentContributors = "AgileSqlClub.DeploymentFilterContributor",
AdditionalDeploymentContributorArguments = "SqlPackageFilter=IgnoreName(SqlQueryNotificationService*)" (without .)
Still a same result :(

Sorry, my mistake.
Now it failed on SqlQueryNotificationStoredProcedure.
So, it seems working for services!!
I'll try AdditionalDeploymentContributorArguments = "SqlPackageFilter=IgnoreName(SqlQueryNotification*)"
and will let you know

Great :)

Hi,

By using this filter:

SqlPackageFilterA=IgnoreSchema(CADIS);SqlPackageFilterB=IgnoreSchema(CADIS_SYS);SqlPackageFilterC=IgnoreSchema(CADIS_PROC) I'm getting log records like this:

Step removed from deployment by SqlPackageFilter, object: [CADIS_PROC].[SPDG_FUNCTION30_INBOXUPD], step type: Alter
Step removed from deployment by SqlPackageFilter, object: [CADIS_PROC].[SPDG_FUNCTION30_INBOXSELSINGLEROW], step type: Alter
Step removed from deployment by SqlPackageFilter, object: [CADIS_PROC].[SPDG_FUNCTION30_INBOXDEL], step type: Alter
Step removed from deployment by SqlPackageFilter, object: [CADIS_PROC].[SPDG_FUNCTION30_INBOXINS], step type: Alter
Step removed from deployment by SqlPackageFilter, object: [CADIS_PROC].[SPDG_FUNCTION47_INBOXSEL], step type: Alter
Step removed from deployment by SqlPackageFilter, object: [CADIS_PROC].[SPDG_FUNCTION42_INBOXSEL], step type: Alter

but actual publish process fails with errors like this:

Error SQL0: The element [CADIS_PROC].[SPDG_FUNCTION30_INBOXUPD] cannot be deploy
ed as the script body is encrypted.
Error SQL0: The element [CADIS_PROC].[SPDG_FUNCTION30_INBOXSELSINGLEROW] cannot
be deployed as the script body is encrypted.
Error SQL0: The element [CADIS_PROC].[SPDG_FUNCTION30_INBOXDEL] cannot be deploy
ed as the script body is encrypted.
Error SQL0: The element [CADIS_PROC].[SPDG_FUNCTION30_INBOXINS] cannot be deploy
ed as the script body is encrypted.
Error SQL0: The element [CADIS_PROC].[SPDG_FUNCTION47_INBOXSEL] cannot be deploy
ed as the script body is encrypted.

Please advise.
Thank you.

Hey Anatoly if you can send me a sample dacpac with a demo of how they don't work I will take a look for you.

Anybody know if these filters work if used as ImportContributors and ImportContributorArguments

Pages

Add new comment