Dacpac must not drop extra columns

Question

I have happily been writing a product which uses a, Sql Server Database Project and life has been good until we discovered a problem in upgrades.

While we create tables, stored procedures and various other database artefacts, once deployed at customers they can add their own columns to the tables created by our dacpac.

We are using DacFx for deployment (Microsoft.SqlServer.Dac) and also provide the raw dacpac for customers who insist deployment by their DBA's.

While the problem may still be present when using SSMS or similar tools, I am certain that with the "right" code we should somehow be able to prevent this when deploying via code.

Has anyone had the same issues and possibly found a solution?

Update, add screenshot for deployment settings. As can be seen in the image, the "Drop objects in target but not in project" setting is already turned off.

Answer 1

Love this statement "I have happily been writing a product which uses a, Sql Server Database Project and life has been good" ha ha!

You could write a deployment contributor that looks for new columns and remove the drop step from the process.

You can either write your own or I have one that should do it (http://agilesqlclub.codeplex.com/), if you use my one then this will probably work for you:

/p:AdditionalDeploymentContributorArguments="SqlPackageFilter=KeepType(.*Column.*)"

If you want to write your own then you can use mine as a guide (source is on codeplex) or see http://blogs.msdn.com/b/ssdt/archive/2013/12/23/dacfx-public-model-tutorial.asp specifically "Solution 2: Filtering at deployment time".

Ed

Answer 2

There is an option "DropObjectsNotInSource" if that is false then the columns will stay (but you will have to drop other objects specifically (say in post-deploy).

There are more options in latest build but don't think you can specify to keep columns only.