Setting up a new CI server to build SSDT projects
I am going to ignore the fact that people don't want to install visual studio on a build server, in the future there will likely be a way to install just the required components rather than everything. Today, the way I see it is that I develop in Visual Studio, I am happy to install Visual Studio on the build server for me Visual Studio is as dependency for SSDT.
Additionally I find it useful to have Visual Studio on a build server to troubleshoot those really odd issues that you sometimes get when a project doesn't build on a build server.
The steps I follow to get a new CI server ready to build SSDT projects are:
Download Visual Studio Express (2015 is good)
Click "Express for Desktop"
This will download the stub installer.
Download the full version of express
Because we have just downloaded the stub and we don't want to do that every time or do not have internet access from our build server run:
This will download the setup files - just under 2gb
Run the installer
On the build server copy the setup files (if they aren't already there) and run "wdexpress_full.exe"
Do the reboot, you can't open express until you have done this, just do it.
Don't be tempted to go off and create a build yet, validate that SSDT works and builds your project correctly so...
Open Visual studio, if it asks you to sign in just say not now thanks.
Open you project in Visual Studio, ensure it builds and creates a dacpac
Once that builds close Visual Studio and open "MSBuild Command Prompt for VS2015" which is a command prompt that can see msbuild, ensure you can build the project and get a dacpac out:
If you have a dacpac that is created by /t:build then you have a good build server.
What about automating this?
Do it if you want to, personally I would wait for the headless build stuff to come from Microsoft. If you have more than a couple of build servers to do then automate it. If your developers have licenses for Visual Studio then you can install the same version which can run unattended installs of Visual Studio.
Of course better than automating the install is to create container using https://spoon.net and sharing that with everyone :)