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.

Fridafriday

So the exact problem is that a customer can create extra columns but when you deploy your DacPac it drops those columns? – Antre

@Nick.McDermaid That is the exact problem, very nicely worded :) – Fridafriday

Hi Louis, did you ever get a good solution to this? I also need to be able to deploy and not drop columns (from selected tables), whilst adding new columns that have been introduced into the database project – Nassi

Hi @SimonGreen, i have the same issue, did you find the solution? – Tenpin

Hi, I didn't find a great solution, no. I wrote a modifier that detects whether the Alter Table Steps are in the plan (in which case I just remove them), or whether the SqlTableMigrationTStep was used, in which case I have to remove the whole step and then put in a whole series of routines to allow the dacpac deployment to complete successfully when the SqlMigrationStep is removed... and in additional I have to then implement the column additions and removals that I want manually but executing old fashioned script like 'if not exists my new column then create it'... – Nassi

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

Maccarthy

Does this actually work? I don't think that Columns are a Type, so would the KeepType command work? – Nassi

LOL you are right for a column change we get a ALTER on the table rather than a create on a table (the column type does exist we just don't see it) - I'll add an extra filter type to keep columns in tables as I have seen this a few times (and I am going to need it myself in an upcoming project) :) – Maccarthy

Hi Ed, I'm very interesting in your progress here. I've been doing some investigation, and I see that the DeploymentPlanModifier receives a single AlterElementStep for a table, which might contain multiple column additions and removals for that table. Is it possible to filter out the removals so that only the column additions get executed? Do you have to remove the AlterElementStep and replace it with a new (cut down) one? – Nassi

Hey Simon, i've release a new version and your right about the AlterElementStep - for a full write up see the.agilesql.club/blog/Ed-Elliott/2015/09/23/… - give it a go and let me know if it solves it for you – Maccarthy

Grab the download from agilesqlclub.codeplex.com/releases/view/617564 (version >= 1.4.1) – Maccarthy

Hi Ed, just had a brief read of your blog, will check the code out later - but not sure about solution #1... if we ignore the SqlTablemigration step completely, we will avoid the drop columns sure, but won't we also miss out on any new columns in the dacpac? – Nassi

Hi Simon, no I only remove drops so new columns are added (they are added at the end though) – Maccarthy

This new version works for me, I just use the argument "SqlPackageFilter=KeepTableColumns(Table1)". Thanks! – Nassi

Hmm, actually no it's a bit broken. If the table has no rows, it works. However, if it does have rows, the AlterElementStep is actually a SqlTableMigrationStep. In this case, it removes the whole step from the plan. If the table has indexes, then the subsequent script expects the table to be dropped (along with its indexes) and then it tries to recreate the indexes. But because the table never gets dropped, the indexes still exist and the script blows up when it still tries to recreate the indexes – Nassi

I wasn't quite correct about one thing - the choice to use a SqlTableMigrationStep is not due to whether there are rows or not. I am seeing a SqlTableMigrationStep being used on a table with no rows. I don't know how it decides to use a SqlMigrationStep, but the problem still remains that if it does decide to use one on a table that has indexes, then you'll get an error – Nassi

...and same applies with Full Text indexes as well as 'normal' indexes – Nassi

Hi Simon - I can't replicate it, also it is quite hard to track in the comments here (and probably against so rules) - can you put the details and ideally a sample dacpac + ddl script to repro it: agilesqlclub.codeplex.com/workitem/list/basic cheers ed – Maccarthy

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.

Piscator

Hi Mark, I am sorry to say but this is not the case from my dealings with this. That setting is off already and I am using the "latest build". – Fridafriday

Columns are not "objects", at least from a Dacpac point-of-view. Setting that flag will prevent tables, views, etc. from being dropped, but not columns. – Snowdrift