Creating code first database delopyment - Migrate.exe and VSTS

Creating database deployment.
My case is EF 6 (in core there would be also possibility to create sql script great article here)
It should be so simple. As a developer you just go to Package Management Console and run Update-Database. Everything is just happening. Well not that everything.

1. You have to set default project to project that should be use to find your code first migrations. 
2. You have to set startup project to one that contains config file with database connection string. 
3. And you have to have valid connection string

If you don't have the startup project set right you get 'enigmatic' exception, that I will address later.
System.Data.SqlClient.SqlException (0x80131904): A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)

Not that much to do. So how to replicate it in script or CI system?

Under the Package Managment Console there is migration.exe tool. You can find it in Entity Framework nuget package folder (in Tools sub-folder) and run it from command line or script.

Run for some help:

Migrate.exe /?

For sure you have to indicate the DLL containing code first migrations. So far so go: the first step is the same. But the connection string configuration is a little bit different. You can indicate path to config file containing connection string or set inline connection string and provider name ("System.Data.SqlClient"). If you make mistake in connection string you will get the same error "A network-related..." in this case it means that migrate.exe cannot find database. Now this enigmatic error have more sens, it's the same tool so it's no difference between wrong connection string and no connection string (default one) in case that we don't set startup project (startupDirectory parameter).

Thing to remember here. All files must be in one directory: migration.exe, dataModel.dll, myconfig.config. If you try to get sub-folder or other folder then the exception will indicate that the file just don't exist.

It is still enigmatic when your connection string is set correctly, further more your migrations run correctly and there is this exception in seed method :/

In my case this strange exception in seed method was caused by custom Identity implementation, well I didn't dive into this problem I just override it with sql scripts included in seed (creating users using SQL instead of C#). This would be a good start to have seed - sql only.

This script is enough to run it like this - when we have desired connection string.

migrate.exe MyCodeFirstDataModel.dll /connectionString="{ConnectionStringFileName}" /connectionProviderName="System.Data.SqlClient"

Getting transformed config file

Normally getting transformed config according to project configuration is done for us while creating deployment packages. Now we need to do it manually.

Working with VSTS starting here, so we have new restrictions.

We can use XDT Transform it's really simple in configuration extension, and it just work. 

In Transformations we need to set 

[what-should-be-set-file] => [main-config-before-changes-file] => [output-file]*

* output file by default is the file before changes so this parameter is not required but helpfull.

Because there is no possibility to use configuration in this transformation like this:

I decided to make all transformations in one run, and then choose correct file when needed. Like this:

If you don't want to use ready extension, or want to know what is going on under the hood (again) you can use this scrip:

The problem is to find Microsoft.Web.XmlTransform.dll because it is in MSBuild tools directory like this:
C:\Program Files (x86)\Microsoft Visual Studio\2017\Enterprise\MSBuild\Microsoft\VisualStudio\v15.0\Web\
Because on VSTS we don't have control over the environment we would have to look for this DLL location and it's not so convenient.

Ready process you can see below. I have a little mess in my artifacts so I copy files to one directory. Migration.exe I also store in artifacts, of course I could get it with Nugget restore, but I choose a lazy solution :) I choose one specific configuration file (though I created all configuration files in first step).
This is working. With one 'but'. Database firewall needs to be configure to allow VSTS IP address.