Learn how to use tSQLt to professionally unit test T-SQL code. To enroll in the free self-paced course visit here

SSDT Dev in Visual Studio Code

  • Posted on: 27 April 2017
  • By: Ed Elliott

I have been quite interested by vs code and have been using it more and more recently. I use it for all my GO (#golang FTW) work and also powershell and I have been toying with the sql tools team's sql extension which is great. For a long time I have thought about bringing the SSDT experience to other IDE's like Jetbrains IntelliJ but because I have been using vscode quite a lot recently and separately I have been doing more and more javascript and typescript I thought it would be interesting to see how hard it would be to write a vscode extension that lets me build dacpac's.

The general goals of this are not to re-created the ssdt experience in visual studio but to provide a lighter, faster way of developing sql code, if I can have an extension that:

  • is fast
  • is light weight - memory is important for dev machines and 2 gb for a large db project is limiting
  • gives us the important things like pre,post deploy scripts, refactoring and the ability to generate dacpac

I am not really interested in providing ui's like the schema compare - for that use SSDT or spend some money on the Redgate tools.

I am also not interested in replacing what the sql tools team are doing, I am happy to leave them to do the harder, important but less interesting things to me like t-sql formatting so with that in mind I have started a new project that is very hacky at the moment, more an experiment to see if it will work but a vs code extension that builds dacpacs:


This is basically just a wrapper around the DacFx so there shouldn't be anything too hard and also because it is windows only for now (until the DacFx is cross platform it will only ever be windows, but I hold out hope for cross platform DacFx one day!).

This works similarly to the sql tools team extension in that there is a .net app that is called by the vs code extension (typescript running on node.js) so if you wanted to try this, download the repo, run the SSDTWrap exe (not under a debugger or you will face t-sql parsing first chance exception performance hell). Then in vs code open the folder "src\s2" and the "extension.ts" file and F5 - this will open a new vs code window - open a folder with your .sql files and it will create a t-sql model and report any errors.

If you do ctrl+shift+p to open the command pallette and then do "build dacpac" it will generate a dacpac for you from the sql files. You will need to put this ssdt.json file in the root of the directory you open in vscode:

"outputFile": "c:\\dev\\abc.dacpac",
"SqlServerVersion": "Sql130",
"type": "same",
"path": "C:\\Program Files (x86)\\Microsoft Visual Studio 14.0\\Common7\\IDE\\Extensions\\Microsoft\\SQLDB\\Extensions\\SqlServer\\140\\SQLSchemas\\master.dacpac"
"PreScript": "",
"PostScript": "",
"RefactorLog": "",
"ignoreFilter": "*script*",
"PublishProfilePath": "C:\\dev\\Nested2\\Nested2.publish.xml"

It doesn't really support a lot of things at the moment but I will add in the support needed to build a dacpac including refactorlog.xml, pre/post deploy scripts and references that we all know and love.

I tested with a folder of 2000 procedures, I tried testing 10,000 but I couldn't get ssdt to load them into a project without crashing (on a top of the range i7, 32gb ssd etc laptop) - in the end I settled for 2000 procs and to build the dacpac the times were:

App time (milliseconds)
Visual Studio / SSDT 5630
VS Code 2051

so as well as handling larger projects it is faster as well, a small project (one proc/table) was about 17 seconds to build the dacpac.

Anyway, it is all a bit of fun and pretty hacky at the moment but I like using vs code anyway and am finding it much more light weight than visual studio so will likely invest some more time in it.

If you feel like trying it out, good luck :)

Updating TSqlModels (DacFx)

  • Posted on: 26 April 2017
  • By: Ed Elliott

This one is for the DacFx nuts out there, it can't be a very big club but judging from the occasional emails I get about it, the quality is very high ha ha.

If you have a TSqlModel and you want to make a change to it, you have a couple of choices:

- Create a new model and copy over everything
- Use AddOrUpdateScript

AddOrUpdate can only update scripts that you add (i.e. Know the name of) so if you get the model from a dacpac you are out of luck.

I recently wanted to remove a file from a model I created so what I ended doing was to re-add a script with this contents:


I didn't try an empty file but this let me remove objects from the model quite nicely.

On another note I found something quite interesting that I missed when building a model.

When you parse a script you get a list of parse errors and the script object, you can add the script object to the model and call validate and even though you got parse errors the model validate might still pass (or parse waa haa haa haaaa).

The total errors are script parse errors plus model validation errors which seems obvious now but I missed it.

Hope it helps someone.

Happy DacFx'ing everyone!


DacFxed - Powershell Nugetized DacFx wrapper

  • Posted on: 2 August 2016
  • By: Ed Elliott

Deploying a dacpac from powershell should be pretty easy, there is a .net api which you can use to open a dacpac, compare to a database and either create a script or get the database to look the same as the dacpac but there are a couple of problems with the approach.

Problem 1 - Is the DacFx installed?

The first problem is whether the DacFx is installed, the chances are if Visual Studio or SQL Server have been installed then it will be there.

Problem 2 - Where is the DacFx installed?

Once you know that the DacFx is available, where is it? Depending how you installed it, whether when you installed Visual Studio or via an MSI it will be installed in a different location. Further if you get a recent build it will be in the 130 folder, older builds the 120 or 110 folder. Which one do you have??

Solution 1

So what I used to do to combat these first two issues is to check into my test projects the DAC folder which includes sqlpackage and just shell out to that to do the install, this works but updates to SSDT come between every 1 and every 3 months, the projects I have done this on are all on old builds of the DacFx and probably will be until something goes wrong and someone updates it. That sucks :(

Solution 2

It was with great excitement, and I don't say that lightly, that in July the SSDT team announced that they would be maintaining a nuget package of the DacFx. This is really exciting because it means that problem 1 and 2 no longer exist, we can simply reference the nuget package and keeping up to date is pretty simple. While you recover from the excitement that is the DacFx in a nuget package I have something else to get you really excited...

This means no install to Program Files

I know right, exciting! What this means is that even on hosted build servers (vsts for example) where we don't have admin rights we can still keep up to date with recent copies of the DacFx without having to commit the source control sin of checking in libraries.

Problem 3 :( Deployment Contributors

If we no longer need admin rights to use the DacFx it means we no longer can rely on admin rights to deploy dacpacs - this means that deployment contributors cannot be copied to program files which is where the DacFx loads contributors from. Remember also that contributors are specifically loaded from the program files (or x86) version of SQL Sever or Visual Studio or whatever version of SSDT you have so it could be from any one of the version DAC folders i.e. 110, 120, 130, 1xx etc.

Solution 3

There is a solution? Well yes of course otherwise I wouldn't have been writing this! DacFxed is a powershell module which:

  • 1. References the DacFx nuget package so updating to the latest version is simple
  • 2. Implements a hack (ooh) to allow contributors to be loaded from anywhere
  • 3. Is published to the powershell gallery so to use it you just do "Install-Module -Name DacFxed -Scope User -Force"
  • 4. Has a Publish-Database, New-PublishProfile and Get-DatabaseChanges CmdLets

Cool right, now a couple of things to mention. Firstly this is of course open source and available: https://github.com/GoEddie/DacFxed

Secondly, what is up with the cool name? Well I did't want to call the module DacFx as I was publishing it to the powershell gallery and hope that one day maybe the SSDT team will want to create a supported powershell module that publishes dacpac's and didn't want to steal the name. DacFxed is just DacFx with my name appended, what could be cooler than that?

In the list I mention that it uses a hack to allow contributors to be loaded, lets talk some more about this.

Currently to use a deployment contributor you either need to copy it into the Program Files directory or use sqlpackage and put it in a sub-folder called Extensions - neither of these two options are particularly exciting. I needed a better way to be able to include a deployment contributor in some user writable folder and then load the dll's from there. I hope (there is a connect somewhere) that one day the SSDT team will give us an option when using the DacFx to say where to load contributors from - when that happens I commit here to modify this package to support their method so if you do use this then fear not, I will make sure it stops using a hack as soon as possible.

What is this so called hack?

When the DacFx tries to load deployment contributors it does a search of a number of well known directories to find the dll's, it also has a fairly unique way to determine which folder it should use when being called from visual studio - what happens is that it checks whether two folders above the folder the dll is in, there is a file called "Microsoft.VisualStudio.Data.Tools.Package.dll" - if this file exists then it searches in the folder the dll is in to find out if there are any deployment contributors to load. The interesting thing about this is that it doesn't actually load the file, just checks the existence of it - if it exists it searches itself for extensions. So if we have this structure:

Folder 1\Microsoft.VisualStudio.Data.Tools.Package.dll (this can be an empty text file)
Folder 1\Folder 2\folder 3\DacFx Dll's

When you load the DacFx dll's from this folder (to be specific "Microsoft.Data.Tools.Schema.Sql.dll") we get the ability to load contributors from user writable folders (which is the end goal for this).

Problem Solved?

Well no, it would be if it wasn't for the way .net resolved assemblies and powershell CmdLets. If our powershell module is structured like this:

WindowsPowershell\Modules\DacFxed\Microsoft.VisualStudio.Data.Tools.Package.dll (remember this is an empty text file)

What would happen is that out Cmdlet.dll would try to resolve the DacFx and it would not find it as .net doesn't search every sub-folder of the current directory to find dll's to load. If .net can't find the dll locally it will search horrible things like the GAC and if it finds the dll there, load it. This means our sneaky trick to trick the DacFx to load our extensions doesn't work.

What I came up with is a DacFxLoadProxy and DaxFxProxy, the CmdLet.dll has a reference to DacFxLoadProxy. DacFxLoadProxy does a Assembly.Load on each of the DacFx dll's in the bin\dll folder and then overrides Assemnbly.Resolve and tries to load the DacFxProxy.dll. Loading the DacFxProxy causes the DacFx dll's to be loaded and the DacFxLoadProxy passes the dll's that it has already loaded from the correct place.

Phew, I said hack :)

This sounds dangerous

Well yes and no, yes it is a little exotic but no in that if you tell the DacFx to load a contributor if this process doesn't work for some reason the worst thing that will happen is you get a "Deployment contributor could not be loaded" error - you won't deploy to a database without contributor you weren't expecting.

So no not really dangerous, just annoying if it doesn't work. I have tests setup and a release pipeline for this that I will cover in another post that make it easy for me to ensure each update to the DacFx can be taken while this still works. If the SSDT team break this behaviour then I won't deploy and then anyone using it can update in their own time.

How does it work?

You need a dacpac and a publish profile, if you have a dacpac and no publish profile then New-PublishProfile will create a template you can use to get started with.

Publish-Database -DacpacPath "path\to\dacpac" -PublishProfilePath "path\to\publish-profile"


Publish-Database -DacpacPath "path\to\dacpac" -PublishProfilePath "path\to\publish-profile" -verbose


Publish-Database -DacpacPath "path\to\dacpac" -PublishProfilePath "path\to\publish-profile" -verbose -DacFxExtensionsPath "c:\path\one;c:\path-two"

Anyway, enjoy!

What is SSDT? Part 3 - an API for me, an API for you, an API for everyone!

  • Posted on: 7 January 2016
  • By: Ed Elliott

In the final part of this 3 part series on what SSDT actually is I am going to talk about the documented API. What I mean by documented is that Microsoft have published the specification to it so that it is available to use rather than the documentation is particularly good - I warn you it isn't great but there are some places to get some help and I will point them out to you.

The first parts are available:




Same as before, i'll give an overview and some links to more info if there are any :)

what is SSDT? It is a lovely API for winners

Documented API

There are a number of different API's broadly split into two categories the DacFx and the ScriptDom. The DacFx consists of everything in the diagram around the API's circle except the ScriptDom which is separate.


For me SSDT really sets SQL Server apart from any other RDBMS and makes development so more professional. The main reason is the declarative approach (I realise this can be replicated to some extent) but also because of the API support - name me one other RDBMS or even NoSql system where you get an API to query and modify the language itself, go on think about it for a minute, still thinking?

The ScriptDom has two ways to use it, the first is to pass it some T-SQL (be it DDL or DML) and it will return a representation of the T-SQL in objects which you can examine and do things to.

The second way it can be used is to take objects and create T-SQL.

I know what you are thinking, why would I bother? It seems pretty pointless to me. Let me assure you that it is not pointless, the first time I used it for an actual issue was where I had a deployment script with about 70 tables in. For various reasons we couldn't guarantee that the tables existed (some tables were moved into another database) the answer would have been to either split the tables into 2 files or manually wrap if exists around each table's deploy script. Neither of these options were particularly appealing at the particular point in the project with the time we had to deliver.

What I ended up doing was using the ScriptDom to parse the file and for each statement, (some were merge statements, some straight inserts, some inserts using outer joins back to the original table and an in-memory table) retrieved the name of the table affected and then generating an if exists and begin / end around the table, I also produced a nice little excel document that showed what tables where there and what method was used to setup the data so we could prioritise splitting the statements up and moving them towards merge statements when we had more time.

Doing this manually would have technically been possible but there are so many things to consider when writing a parser it really is not a very reliable thing to do, just consider these different ways to do the same thing:

select 1 a;
select 1 a
select /*hi there*/ 1 a
select * from (select 1 one) a
select 1 as a;
select 1 as a
select /*hi there*/ 1 as a
select 1 as [a];
select 1 as [a]
select /*hi there*/ 1 as a

select * from (select 1 one) a
;with a as (select 1 a) select * from a
;with a as (select 1 as a) select * from a
;with a as (select 1 a) select * from a
;with a(a) as (select 1) select * from a
;with a(a) as (select 1 a) select * from a

select 1 a into #t; select a from #t; drop table #t;
select 1 a into #t; select a a from #t; drop table #t;
select 1 a into #t; select a as a from #t; drop table #t;

I literally got bored thinking of more variations but I am pretty sure I could think of at least 100 ways to get a result set with a single column called a and a single row with a value of 1. If you think that parsing T-SQL is something that is simple then you should give it a go as you will learn a lot (mostly that you should use an API to do it).

One thing that causes some confusion when using the ScriptDom is that to parse any T-SQL unless you just want a stream of tokens you need to use the visitor pattern and implement a class that inherits from TSqlFragmentVisitor - it is really simple to do and you can retrieve all the types of object that you like (CreateProcedure, AlterProcedure etc etc).

So if you have a need to parse T-SQL then use the ScriptDom, it is really simple what is not so simple is the other side of the coin, creating and modifying objects to create T-SQL.

If you need to do this then it is quite hard to work out the exact type of objects you need at the right point, for example if you take this query:

;with a as (select 1 a) select * from a

What you end up with is:

  • SelectStatement that has...
  • a list of CommonTableExpression that has...
  • an ExpressionName which is of type Identitfier with a value of "a"
  • an empty list of Identitiers which are the columns
  • a QueryExpression that is of type QuerySpecification which has...
  • a single LiteralInteger as the expression on a SelectScalarExpression as the only element in a list of SelectElement's
  • the CommonTableExpression has no other specific properties
  • the SelectStatement also has...
  • a QueryExpression that is a QuerySpecification which contains....
  • a list of SelectElement's with one item, a SelectStarExpression
  • a FromClause that has a list of 1 TableReference's which is a NamedTableReference that is...
  • a SchemaObjectName that just has an Object name

If you think that it sounds confusing you would be right, but I do have some help for you in the ScriptDomVisualizer - if you give it a SQL statement it will parse it and show a tree of the objects you will get. If you do anything with the ScriptDom then use this as it will help a lot.

ScriptDom Visualizer V2


Using the TransactSql.ScriptDOM parser to get statement counts


MSDN forum post and a great demo of how to parse T-SQL from Gert Drapers


TSql Model

The TSql Model is a query-able model of all the objects in an SSDT project, their properties and relationships. That sounds like a mouthful but consider this:

create view a_room_with_a_view
select column_name from table_name;

If you just have this script without the model you can use the ScriptDom to find that there is a select statement and a table reference and you could probably also work out that there is a column name but how do you know that there is actually a table called table_name or a column on the table called column_name and also that there isn't already a view or other object called a_room_with_a_view? The TSql Model is how you know!

The TSql Model is actually not that easy to parse (there is help so fear not, I will tell you the hard way to do it then show you the easy way). What you do is to load a model from a dacpac (or you can create a brand new empty one if you like) and then query it for objects of specific types or with a specific name or even just all objects.

So imagine you open a dacpac and want to find the a_room_with_a_view view you could do something like:

var model = new TSqlModel(@"C:\path\to\dacpac.dacpac", DacSchemaModelStorageType.File);
var view = model.GetObject(ModelSchema.View, new ObjectIdentifier("a_room_with_a_view"), DacQueryScopes.UserDefined);

If you then wanted to find all the tables that the view referenced then you could examine the properties and relationships to find what you want. It is confusing to get your head around but really useful because if you know what type of object you are interested in then you can tailor your calls to that but if you just what to find all objects that reference a table (i.e. views, other tables via constraints, functions, procedures etc) it means you can really easily do that without having to say "get me all tables that reference this table, get me all functions that reference this table etc etc".

The TSql Model API returns loosely typed objects so everything is a TSqlObject - this is good and bad but I will leave it as an exercise for you to find out why!

DacFx Public Model Tutorial

This is what first allowed me to get into the DacFx, it is the only real documentation I have seen from Microsoft and invaluable to get started


Dacpac Explorer


I wrote DacPac Explorer to help teach myself about the DacFx and it turns out it is quite useful and has even been used within Microsoft as a training tool so there you go!

Querying the DacFx API – Getting Column Type Information



If you have tried to do something like get the data type of a column you will appreciate how much work there is to do, well as a special treat there is a github project called Microsoft/DacExtensions and it was written by members of the SSDT team in Microsoft but is open source (I love that!) what it does is take the loosely typed TSqlModel objects and creates strongly typed wrappers so if you want to see what columns are on a table, you query the model for objects of type TSqlTable (or a version specific one if you want) and you get a list of columns as a property rather than having to traverse the relationships etc.

If you do any serious querying of the TSqlModel then look at this as it really will help!



Build Contributors

The last three items, the contributors all let you inject your own code into something that SSDT does and change it - this really is huge, normally with tools you get the resulting output and that is it your stuck with it but with SSDT you can completely control certain aspects of how it works.

When you build a project in SSDT a build contributor gets full access to the validated TSqlModel and any properties of the build task so if you wanted to do some validation or change the model when it had been built then you can use this.

Customize Database Build and Deployment by Using Build and Deployment Contributors


Deployment Plan Modifiers

When the DacServices have compared your dacpac to a database, deployment plan modifiers are called and can add or remove steps in the plan before the final deployment script is generated. Again this is huge, it is bigger than huger, it is massive. If you want to make sure a table is never dropped or you don't like the sql code that is generated then you can write a utility to change it before it is created - write the utility and use it for every build.


Inside an SSDT Deployment Contributor


Repository of sample deployment contributors


Deployment Contributor that lets you filter deployments (don't deploy x schema to y server etc)


Deployment Plan Executor

Where deplotment plan modifiers can change the plan and add, edit or remove steps a plan executor gets read only access to the plan and is called when the plan is actually executed. The example on MSDN shows a report of the deployment to give you some idea of what you can do with them.

Walkthrough: Extend Database Project Deployment to Analyze the Deployment Plan


Help and Support

I created a gitter room to answer questions and give advice on writing deployment contributors but I would be more than happy to help answer questions on them or any part of the DacFx so feel free to drop in:


All welcome :)

Using the ScriptDom without losing comments

  • Posted on: 24 November 2015
  • By: Ed Elliott

The ScriptDom is cool, it lets you parse T-SQL, play around with the AST (tree of statements) and then generate T-SQL again (probably in a different format etc). This is really cool but if you round-trip the T-SQL into an AST and back you lose some important information like comments so, if you do something like:

var original = @"create procedure [dbo].[get_employee](@employee_id int)

select 100;

select * from a a1 where a1.a like '%fff%';
var parser = new TSql120Parser(false);

IList errors;
var fragment = parser.Parse(new StringReader(original), out errors);

var outputScript = new StringBuilder();

var generator = new Sql120ScriptGenerator();
generator.GenerateScript(fragment, new StringWriter(outputScript));

What you would end up with is instead of:

create procedure [dbo].[get_employee](@employee_id int)

select 100;

select * from a a1 where a1.a like '%fff%';

You get:

CREATE PROCEDURE [dbo].[get_employee]
@employee_id INT
FROM a AS a1
WHERE a1.a LIKE '%fff%';

So if you wanted to do something like change all the keywords to upper-case it would be really simple but you would lose the comments which I know some people are quite attached to.

To get around this there are two ways that I can think of, if anyone has another way please shout!

Approach 1 - replace just the parts you want to

When you parse the t-sql you get back a TSqlFragment, all of the ScriptDom statements like CreateProcedureStatement inherit from TSqlFragment (by way of a TSqlStatement and probably a whole load of other things) what this means is that as well as being able to maniplulate the contents of the statement, we get the offset into the start of the string that was parsed and the length of the fragment. So for example if we wanted to change the type of the parameter, we would use the ScriptDom to find the parameter, we would then have the StartOffset and the Fragment length. Using those two values we can do a replace on the sub-string with whatever we want to put in its place.

Approach 2 - modify the TSqlToken on the fly

The second approach is to get the parser to parse the script and create the TSqlFragment but instead of using the AST and modifying the TSqlStatements, you can iterate through all of the tokens and modify the ones you don't like. For instance if you wanted to change:

create procedure abc
select 100;


alter procedure abc
select 'ABC';

You could simply do a find and replace on "create procedure" but what about cases like "create /*frrrrrrr*/ procedure" or something else entirely, manually parsing it isn't trivial so instead we can use my second approach which is to modify the tokens. In this case to change the create into an alter we can do something like:

bool haveCreate = false;
var output = new StringBuilder();

foreach (var token in fragment.ScriptTokenStream)
if (!haveCreate && token.TokenType == TSqlTokenType.Create)
var alterToken = new TSqlParserToken(TSqlTokenType.Alter, "alter");
haveCreate = true;
return output.ToString();

So "round-tripping" t-sql via a ScriptDom parser and generator does lose some of the content which may be useful but all is not lost, here are a couple of approaches, if anyone has some more please share!

Blocking SSDT Deployments When You Don't Like Something

  • Posted on: 6 November 2015
  • By: Ed Elliott

This is probably the last contributor that I am going to add to the DacFx deployment contributors project (https://github.com/DacFxDeploymentContributors/Contributors/) for a little while, if anyone else has one they want to contribute please feel free. I have been pretty pleased with how they went but want to make the testing side better.

This is an example contributor that is different to all the others in that the previous contributors enumerated the list of steps and either modified one or swapped it with another but this one uses the context object that is passed in to find all changes to stored procedures and then based on the dacpac and deployment database made a decision about whether to fail the deployment or not:


Things to point out here are that this works on context object that is of type DeploymentPlanContributorContext that is passed in to OnExecute. To get a list of changes instead of enumerating the steps it finds which ones have been changed rather than the actions that are to be carried out:

foreach (var changeDefinition in context.ComparisonResult.ElementsChanged.Keys)
var change = context.ComparisonResult.ElementsChanged[changeDefinition];

if (change.TargetObject.ObjectType == ModelSchema.Procedure)
VerifyStoredProcedureChange(context, change);

This is quite a subtle difference than enumerating the steps but is actually really useful, aside from changed objects you can find objects to be created and / or dropped. This ability to see the intentions rather than the steps means that we do not have to aware of things like a table alter to add a constraint is actually a new object rather than a change of the table object.

This contributor is also different in that it doesn't use the ScriptDom (I know for national ScriptDom Heart week I should have done better!) instead it uses the TSqlObject querying to find parameters on changed procedures and verify that the change should be allowed. If the change is not allowed the it fails the deployment by publishing an ExtensibilityError with a severity of Error. This means that the deployment is effectively blocked.

Why this contributor?

This was just to show how we can do other things in contributors than just modify the deployment plan, we can validate rules than can only be validated when we have access to the source and target models and that happens at deployment time only. The rules here are to check whether a parameter is added to a procedure without a default, so if there were existing applications using the procedure they could break.

I am not sure if this is something that I would actually want to do, my preference would be to have a complete set of integration tests for the application and ensure that nothing is broken when the change has been made (in a test environment) rather than trying to protect production but still a fun contributor to do.

Enjoy and as always if you have any questions ping me, or if it is about a deployment contributor try our gitter chat room (open to all):



SQL Server Edition aware SSDT deployment scripts

  • Posted on: 5 November 2015
  • By: Ed Elliott

Another day another deployment contributor for the project: https://github.com/DacFxDeploymentContributors/Contributors.

This one came about because I was talking to Peter Schott (b|t) over at the deployment contributor gitter room and he has dacpac he wants to deploy to different editions of sql server and enable different options when deploying to enterprise edition.

One of the things I found out is that from a contributor, although you have the source and destination models you do not have access to any information about the server other than the version. To get around this you can either pass in an arg to say which edition the compare is running against or if you leave that off then the create index statement is duplicated and wrapped on an check against @@version to determine the edition which is more t-sql than I would like but should work.

One other thing I have realised is that I have by default used the generators for the 120 version of SQL but in the contributors I could get the version from the context and use the correct one - maybe a task for the next one.

Finally I have changed the message box at the beginning to a Debuger.Launch which works much better - thanks to Ken (blog)

After a random twitter conversation I have a great new contributor to write tomorrow - I can't wait :)

Deploy SSDT INSERTS in Batches

  • Posted on: 4 November 2015
  • By: Ed Elliott

I have added my first actual deployment contributor to my deployment contributor project:


The idea is basically for people to share ideas about writing deployment contributors for SSDT and help each other. If you don't know what a deployment contributor is then you are missing a real treat. A deployment contributor lets you modify the t-sql that SSDT creates to upgrade a database from one version to the next.

What is the contributor?

In SSDT sometimes you get a table migration which is where you want to do something like add a column in the middle of a table, instead of SSDT just adding it at the end it creates a new table, inserts the data from the old table then drops the old table and renames the new table to match the old ones name.

I thought it would be interesting to write a contributor that searched for these table migrations and changed the insert portion into a batched insert that does the insert in batches so your transaction log growth is manageable on very large tables.

Why bother?

I think there is a use for this, I do not think that it is something that I would lose any sleep over if I did not have, it was more to see how to do it and give an example of writing a deployment contributor that did something quite advanced and show a few things like how I set up integration tests to make testing easy as unit / integration testing deployment contributors is quite hard if you are new to it. I do not know if what I have done is the best way but it works for me but I would love to hear suggestions on how to improve things so please either send some pointers or a pull request.

Solution overview

The first thing is to look at the solution in visual Studio, as you can see from the screen shot I have three projects:

AgileSqlClub.BatchedTableMigration - C# Dll Project
TestDacpacDeploy - SSDT Project, this will create the dacpac I will deploy from
IntegrationTests - C# Dll Project with NUnit via nuget

3 projects, 2 c# dlll, 1 ssdt


I'll start with the integration tests as these are probably the most useful part of this whole thing. I have created a folder called "sqlpackage" and added the dlls and exe needed to run sqlpackage.exe. This at first seems like a bit of an odd thing to do but it makes testing easier, I add the files and set the msbuild property "Copy to Output Directory" to "Copy if newer" - this means in my tests I can copy the deployment contributor into .\sqlpackage and then run .\sqlpackage\sqlpackage.exe and know that my dll will be in the right place to be loaded for the tests.

If you don't do this you will need to copy your dll into the program files directory which is bad because of a) permissions and b) you can only run a single build at a time (without using something like https://turbo.net!).

If you do this in your integration tests your life will be easier writing deployment contributors :)

I have also added two helper classes DacpacDeploy and Database. The Database class connects to a database and sets up the schema as I want it, it feels odd writing deploy scripts but it is only small. If I don't do this when I run sqlpackage.exe to do the compare I won't know what state it is in and might not get the table migration I need.

DacDeploy basically copies the dll that is built by the main c# project into the sqlpackage folder and runs sqlpackage.exe with the args needed to load the contributor and also passes the batch size to use as a parameter.

Finally I have the test itself which is pretty simple it uses DacpacDeploy to create a deployment script and then it checks to see whether the script has the new table migration script in it.

I should really add integration tests that actually run the deployment script and validate the data but it is just gone midnight so I think that is enough for now :)


This is the actual contributor and there are a couple of things that help, in "DeploymentFilter.cs" you need to have an attribute that declares that you have a deployment contributor:

[ExportDeploymentPlanModifier(Name, Version)]
public class DeploymentFilter : DeploymentPlanModifier
private const string Name = "AgileSqlClub.BatchedTableMigration";
private const string Version = "";

I use fields for the name and version as there is a way to output messages from your contributor and I like to make it clear where the message has come from so I re-use these fields for that.

The next part is the method that is actually called when a deployment plan has been generated which we can parse and modify if we like (we like):

protected override void OnExecute(DeploymentPlanContributorContext context)
if (context.Arguments.ContainsKey("EasyDebugBatchedTableMigration"))
MessageBox.Show("Breaking to let you attach a debugger");

This is really useful for debugging contributors, I have a parameter you can pass in which just shows a message box. You can run sqlpackage.exe and set this message box and when it appears attach visual studio, set your breakpoints and then press OK and you can step through your code. There are other ways to get to step into your code when running outside of a process you control but this always seems the simplest to me.

The "context" argument that is passed in is really useful it gives you access to all the options that were set for the comparison as well as the source and target models and any arguments that were passed in.

I then iterate through all the steps in the plan. The steps are typed as DeploymentStep but typically are other types that inherit from DeploymentStep so to find a table migration I just check whether the step is a SqlTableMigrationStep:

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

if (current is SqlTableMigrationStep)
var batched = new MigrationStepBatcher(current as SqlTableMigrationStep, rowCount).BatchStep();

Remove(context.PlanHandle, current);
AddBefore(context.PlanHandle, next, batched);

The MigrationStepBatcher is what actually kicks off the process and Remove takes away the original table migration and AddBefore adds my new one - these methods are provided to us by inheriting from "DeploymentPlanModifier".

The MigrationStepBatcher creates a DeploymentStep of our own called a BatchedSqlTableMigrationStep, to implement your own step you just need to inherit from DeploymentStep and override GenerateTSQL() which returns a list of strings which are each batch in your step. A step can contain one or more batches which are automatically separated for you using GO commands.

When we have a table migration to change into a batch there is a whole load of information we need like the table names, the column definitions etc so I ask the original table migration for it's t-sql by calling GenerateTSQL on it and then parsing that with the ScriptDom - I then also use the ScriptDom to create the output t-sql.

Using the ScriptDom isn't straight forward, it is quite complicated to know exactly what you need to do when so to help I wrote the ScriptDom visualizer which you give it some t-sql and it builds a tree of exactly what types of object the ScriptDom expects are which point and what properties you need to set - if you do anything with the ScriptDom try using this as it saved me a lot of time. If you do write stuff for the ScriptDom and you have something better, please share it!

The actual changes

The idea of this is to take this code that is generated by default in SSDT:

INSERT INTO [dbo].[tmp_ms_xx_ForcedTableMigration]
SELECT [count]
FROM [dbo].[ForcedTableMigration]


WHILE (SELECT count(*)
FROM [dbo].[ForcedTableMigration]) > 0
WITH to_delete
AS (SELECT TOP 1480 [count]
FROM [dbo].[ForcedTableMigration])
DELETE to_delete
OUTPUT deleted.* INTO [dbo].[tmp_ms_xx_ForcedTableMigration] ([count]);

Why not just use regex and a string builder?

You could certainly do that but if you have ever tried doing it that way you will know it is brittle. Do this sort of thing with the ScriptDom, it is more work up front but you can make changes easily and safely and the more people use and share stuff built using the ScriptDom the easier it will get for everyone.

The interesting thing here is the use of the OUTPUT INTO clause, without that I would have only really been able to do it to tables with a unique key as I could select a certain amount of rows but wouldn't have been able to guarantee that I delete those same rows. Using DELETE and OUTPUT INTO means all I need is the column definitions - also deleting from a CTE deletes from the underlying tables, every day is a school day and one I was very pleased to attend today.


To use this contributor, build the project, take the dll and put it in your sqlpackage folder or wherever you need to for the tool you use to deploy and add these arguments:

/p:AdditionalDeploymentContributors=AgileSqlClub.BatchedTableMigration /p:AdditionalDeploymentContributorArguments=BatchedTableMigrationBatchSize=1480;

1480 is the batch size, you will probably want to make this bigger.

Enjoy, as always any questions please ask away :)

ScriptDom Visualizer

  • Posted on: 3 November 2015
  • By: Ed Elliott

Writing code to generate TSql using the ScriptDom is hard. There is a seemingly bewildering array of objects you can use and where you use different types is unclear, I think this is due to the complexity of the task, it really isn't simple and dealing with t-sql gets complicated very quickly - to be clear the ScriptDom is a great tool and although is difficult to use is 1 million times better than writing custom parsers / generators for t-sql.

To help I have written the ScriptDom visualizer which parses some t-sql and produces a tree of the different objects and properties that the ScriptDom needs for you to generate the same query via the ScriptDom rather than via text.

Why bother?

The ScriptDom is cool as it lets us generate valid t-sql as well as parsing t-sql, modifying and re-generating it so we can start to build our own tools to help sql developers - wahoo.

If you have no need to use the ScriptDom then this is a useless tool, if you need to interact with the ScriptDom in any way and you need to know what properties and types to use then this will be useful for you.

I have written it for me to use but am sure others will find it useful, it is as always on github, if you want to change anything just submit a pull request.


Look ma, pretty colours

script dom visualizer, pretty

Grab it from:


Shared SSDT Deployment Contributor Repository and Free Help

  • Posted on: 2 November 2015
  • By: Ed Elliott

One of the best features of SSDT is deployment contributors. They are so exciting I am going to say it again with more gusto. One of the best features of SSDT is deployment contributors.

What these do is let you examine or modify the deployment script that is generated when SSDT compares a project to a database.

why is that cool?

Please imagine for a minute a scenario where you have used SSDT to generate a deployment script and you have looked at it and scoffed that you could have written a better upgrade script. Perhaps you have seen that it has done a table migration and uses a single insert to move all the rows from one table to the next. You have looked up from your script, gazed out of the window and dreamed of a world where instead of a single insert the insert happened in batches. You throw down your script, grab your keyboard from your desk and manually write the upgrade script. A few days later you need to do the same thing so again gaze out of the window dreaming of a batched insert script at which point you grab your keyboard and bash out another script.

Instead of wasting your time writing the same script over and over again you could write a deployment contributor which would enumerate all the steps in a deployment plan. When you find a table migration you could modify it so that it does a batched insert rather than a single insert and use the deployment contributor each time a script is generated. You can now dream of new and exciting modifications you could make to the standard deploy scripts.


I have created a new github repo specifically for deployment contributors as if someone writes a contributor like I just mentioned I could see other people using it:


I have also added a gitter chat room to help people write contributors or use existing ones:


If you have a contributor you would like to commit please submit a pull request or contact me and I can do it for you.

If you want any help writing a contributor I would be pleased to help to get you started and point you in the right direction.


Site Search with Duck Duck Go