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:

Steven Green [MSFT]

January 15, 2015 - 17:25

This is great work, Ed.

This is great work, Ed. Cheers!

Ed Elliott

January 16, 2015 - 00:00

Thanks Steven, I hope

Thanks Steven, I hope someonefinds it useful!

Arno

February 11, 2015 - 12:30

Hi Ed. First of all, thanks

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.

Ed Elliott

February 12, 2015 - 07:54

Hey Arno - probably the

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!

Arno

February 12, 2015 - 08:55

Required contributor could not be loaded error

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

Ed Elliott

February 12, 2015 - 21:28

Great thanks for the feedback

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

Matthew Eno

March 30, 2015 - 03:06

How do I filter synonyms?

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?

Ed Elliott

March 31, 2015 - 16:34

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

Anonymous

June 16, 2015 - 07:53

calling dll from c# code

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?

Ed Elliott

June 16, 2015 - 08:00

Hi Anonymous (if that is who

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.

halil

August 28, 2015 - 13:27

you should right click on dll

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

Anonymous

October 9, 2015 - 14:45

Awesome, thanks! This

Awesome, thanks! This suggestion fixed it for me

halil

August 28, 2015 - 06:40

I want to keep table named

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

Anonymous

September 22, 2015 - 13:58

Keeping columns

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.

Ed Elliott

September 22, 2015 - 14:08

At a guess you can probably

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…

Nataly

March 16, 2016 - 09:16

I need a help

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?

Ed Elliott

March 16, 2016 - 09:28

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

Anonymous

March 16, 2016 - 09:59

The first thing that I tried

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

Anonymous

March 16, 2016 - 10:03

Sorry, my mistake.

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

Ed Elliott

March 16, 2016 - 10:06

Great :)

Anatoliy Zhyzhkevych

February 5, 2017 - 02:13

Ignoring schemas with encrypted objects

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.

Ed Elliott

February 10, 2017 - 16:09

Hey Anatoly if you can send

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.

Brian Schmitt

March 29, 2017 - 19:16

What about import?

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

Larry Steele

August 7, 2017 - 22:18

JIT debugger error

I ran across your app to assist SQLPackage with Ignoring schemas. I’m running into an issue. I keep getting a JIT debugger window come up when I run this from the command line. Here is my command:

D:\Apps\Microsoft Visual Studio 14.0\Common7\IDE\Extensions\Microsoft\SQLDB\DAC\130\Sqlpackage.exe /Action:Script /SourceFile:“D:\temp\sqldeploy\HomeDesigner.dacpac” /TargetConnectionString:“Data Source=xxx;Integrated Security=False;Initial Catalog=HomeDesigner;” /Profile:“D:\temp\sqldeploy\AutoDeploy_BLC.publish.xml” /OutputPath:“D:\temp\test_schema.sql” /p:AdditionalDeploymentContributors=AgileSqlClub.DeploymentFilterContributor /p:AdditionalDeploymentContributorArguments=“SqlPackageFilter0=IgnoreSchema(lot);SqlPackageFilter1=IgnoreSchema(dt);SqlPackageFilter2=IgnoreSchema(com);SqlPackageFilter3=IgnoreSchema(int);”

I’ve copied the AgileSqlClub.SqlPackageFilter.dll file to the package directory. The strange thing is, if I cancel the JIT debugger window, the desired result is in the script to which I pass that as an action. I’m not certain whether or not this is version related as I’m using the Visual Studio 15 path for accessing the SQLPackage although I did build it in VS 2017.

Other than the JIT window coming up, I cannot find any other error logging that would elude to an error I can fix. I am also running this on 2012 from an Administrator command prompt.

Ram

March 20, 2018 - 15:46

IgnoreSchema filter unable to prevent data check on excluded

AgileSqlClub.DeploymentFilterContributor is working like charm and I am able to make significant progress in automating my DB deploymets, Thanks to authors of this Filter.
I am using SqlPackageFilter to ignore DB objects (that doesn’t belong to xxx schema) in the script generation process.
Example: SqlPackageFilter=IgnoreSchema!(xxx)
This is working as expected.
Only problem that I am currently facing is with data check that is happening against newly created constraints.
I see the objects that belong to excluded schemas also being considered for checking existing data against newly created constraints. This data check is happening after post deployment script is executed.
Is there a way to prevent this Data check on objects that belong to excluded schemas?

Ram

March 20, 2018 - 16:26