In my deployment filter I have been asked a couple of times to allow columns that exist on a table that do not exist in a dacpac to be ignored so that they are deleted. The typical scenario is that a vendor supplies a database and then customers can add extra columns (I know what a horrible place to be but it happens).

I had a look at this and my first thought was that if we use the deployment filter and add an IgnoreType(SqlColumn) that it would work but it obviously doesn’t. What I ended up doing is adding a new filter type specifically for table columns and it actually turned out to be quite an interesting jaunt into the ScriptDom and Microsoft.Data.Tools.Schema.Sql.dll so I thought I would blog it because it also strays into understanding how the DacFx builds the deployment scripts for changes to tables (when do you get an Alter Table Add Column and when do you get a Create/Insert/Drop Table).

The context

SSDT has an api called the DacFX api which is literally the most awesome thing that I have seen from Microsoft. It isn’t particularly easy to use and has a few different parts but one part the ScriptDom gives you a way to evaluate, modify and create T-SQL DDL/DML statements - think Roslyn for C# but for SQL Server. When you use SSDT to deploy a dacpac to a database (or generate a script etc) it has some hooks that you can use to modify the steps in the deployment and I have a generic deployment filter to remove steps (http://agilesqlclub.codeplex.com).

In the deployment filter we hook into the api using

protected override void OnExecute(DeploymentPlanContributorContext context)

.

What we get is a list of steps, a step can be one or more things to do. For example if you add a procedure to your dacpac then the step will be a CreateProcedureStep which inherits from CreateElementStep. There are more complex things like a SqlTableMigrationStep which is more than just one step for example:

BEGIN TRANSACTION;

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

SET XACT_ABORT ON;

CREATE TABLE [dbo].[tmp_ms_xx_Employees] (
[EmployeeId] INT NOT NULL,
[Name] VARCHAR (25) NOT NULL,
PRIMARY KEY CLUSTERED ([EmployeeId] ASC)
);

UPDATE [dbo].[Employees]
SET [Name] = ’’
WHERE [Name] IS NULL;

IF EXISTS (SELECT TOP 1 1
FROM [dbo].[Employees])
BEGIN
INSERT INTO [dbo].[tmp_ms_xx_Employees] ([EmployeeId], [name])
SELECT [EmployeeId],
[name]
FROM [dbo].[Employees]
ORDER BY [EmployeeId] ASC
END
DROP TABLE [dbo].[Employees];

EXECUTE sp_rename N’[dbo].[tmp_ms_xx_Employees]’, N’Employees';

COMMIT TRANSACTION;

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

Inheritance without the Inheritance Tax

I don’t know about every country but the way tax works in the UK is that if you earn any money you pay taxes, when you die if you haven’t given all your money away more than 7 years before you die you have to (well technically you can’t) pay tax on the money and property that is left. With the DacFx there is a lot of inheritance and instead of you having to pay tax so giving your friends, family and cat sanctuaries less with each level of inheritance you get more :)

Our deployment filter is passed a series of DeploymentStep’s which can actually be any one of a lot of different types. In this example what I want to do is stop two things, the first is any alter table steps that drop any columns and to be more specific, if we have an alter table, I want to remove any drop column statements and if the drop column statements are the only things the alter table wants to do then I will remove the entire step but if it wants to do anything else like add a column or do anything with a constraint then I want to leave it in the deployment plan but just with the drop columns removed (dear reader, if you didn’t breathe in that last very long sentance you may now).

The second thing I want to do is watch for table rebuilds which end up with the SQL above, i.e. create a temporary table, insert all the data from the original table and then drop the original table, rename the new table and then exit.

This has to be stopped because the columns that exist in the database but not the project will not be included in the create table - I could add them in if I had access to the database but it seems a little complicated for now.

So we have two scenarios:

• 1. Stopping any table migrations
• 2. Stopping any DROP COLUMN statements, but allow other things like create trigger on the table

Stop table migrations

Reminder: a table migration is the SQL we see above and we want to stop because we don’t want to lose any columns that are not defined in our dacpac - remember this is an edge case and not for everyone :)

Our Deployment Contributor overrides

OnExecute(DeploymentPlanContributorContext context)

and we keep pulling off steps from the plan until we have no steps left:

var next = context.PlanHandle.Head;
while (next != null) {
var current = next;
next = current.Next;
}

“current” is of type DeplpoymentStep and this could actually be any one of many different types so what we want to do is see if it is actually something that inherits from AlterElementStep - although our table migration does a create and drop it is actually modifying an existing object so (I believe) it is always an Alter:

var alterStep = current as AlterElementStep;
if (alterStep != null)
{
}

If it is an alter element I have written some rules to then decide what to do with it:

if (step is SqlTableMigrationStep)
{
return true; //we can’t allow a table migration on this table as it would drop our extra columns….
}

returning true here, ends up back in the deployment filter which removes it from the plan (the method is basically, should we remove the step from the plan)

So we have another level of inheritance, DeploymentStep is inherited by AlterElementStep which is inherited by SqlTableMigrationStep - so if I remove this from the plan my table will not be dropped and re-created - this isn’t ideal but is a tradeoff for having to deal with something that smells rather bad in the first place - columns in tables but not dacpacs.

You could modify the step

I wrote that in big because the default table migration is quite dumb, create a table, insert all the data, drop original etc - it wouldn’t be very difficult to do something like wrapping the insert in a while statement and inserting in batches or something, whatever you can image you can do (ish)

Removing column drops

If getting to the table migration and removing it was that simple, removing a drop table must be simpler still? Well not exactly, the table migration is wrapped in its own class, with the drop table we have a hierarchy (not inheritance) of things to do, take a deep breath, grab a beer or a good cigar and lets go through it:

First we get the TSqlScript that is on the Alter statement:

var alterStep = step as AlterElementStep;

if (alterStep == null)
return false;

var script = (TSqlScript)alterStep.Script;

The TSqlScript object should be familiar to anyone who has heard the words “Visitor Pattern” and ScriptDom :). The script has a list of Batches, from poking around if there is more than one batch the first one will contain the drop columns so I can do this:

var batch = script.Batches.FirstOrDefault();

This isn’t documented but I have been poking around with WinDbg / sos and I think it is accurate so…we then take the statements that are on the batch and see if any are an AlterTableDropTableElementStatement’s - that seems like a mouthful but if you break it down you get “AlterTable” “DropTableElement” “Statement” - which sounds like it perfectly matches “Alter Table Blah” “Drop Column BlahBlah” and it is an element (everything is an element in some sort so it fits not just perfectly but exactly):

var dropTableElementStatement = statement as AlterTableDropTableElementStatement;

The AlterTableDropTableElementStatement has a list of things it would like to drop and we want to remove anything that is in that list and has a TableElementType of Column (remember the statement is an AlterTableDropTableElementStatement rather than an AlterTableAdd

dropTableElementStatement.AlterTableDropTableElements.Where(p => p.TableElementType == TableElementType.Column).ToList();

I save this as a separate list so that I can remove them individually from the list, if you do not then you will get an error as you will be enumerating the list while removing from it (crashtastic).

Finally I do a check to see if, after I have removed all of the columns to be dropped, there are any other things to do in the alter table such as dropping constraints then I let the alter table statement stay in the plan but if there is nothing else to do then I remove it from the plan.

All done

So that seems like it to me, I will put a warning about the new feature as although I think it will be ok there are some assumtions I have made as it isn’t documented other than the types being listed on MSDN

(https://technet.microsoft.com/en-us/library/microsoft.sqlserver.transact…(v=sql.110).aspx).

Hopefully if anyone is thinking of writing a deployment contributor this has sort of helped explain what to do, the full source for the filter is available:

http://agilesqlclub.codeplex.com/SourceControl/latest

Happy DacFx’ing :)