TeamCity/.sqlproj build via MSBuild -- FAILED

Question

It all started with the VS2017 runner type within TC. It was failing without any errors or hints (except the (default targets) -- FAILED. message on the solution level). I narrowed it down to the .sqlproj'ects. These are not building via MSBuild, they do compile perfectly within VS2017 on the build agent. I was blaming SSDT for it... but I guess I checked everything. This is the problem which bubbles up to the solution level within the TC build:

I noticed that for VS2017 SSDT comes with the VS setup. The "Workload" "Data storage and processing" is installed and so is "SQL Server Data Tools" under "Individual components". I noticed on https://learn.microsoft.com/EN-US/sql/ssdt/download-sql-server-data-tools-ssdt the sentence "If you are using SSDT with Visual Studio 2017, install the AS and RS components". Did it. Nothing changed. SSDT is also installed separately for previous versions.

This stack is related but it didn't help me.

Any other ideas?

---

I came a step further. Beside VS the Build Tools are installed as well on the build agent and TeamCity's runner for VS2017 obviously uses MSBuild from the Build Tools. As you can see above I was reproducing the issue with MSBuild from the Build Tools as well. If I pick the MSBuild "version" of VS2017 it works like a charm (as it does right within VS). To make it short:

DOES NOT WORK

C:\Program Files (x86)\Microsoft Visual Studio\2017\BuildTools\MSBuild\15.0\Bin

WORKS

C:\Program Files (x86)\Microsoft Visual Studio\2017\Professional\MSBuild\15.0\Bin

Answer 1

I found a blog post from Lukie Briner, his last paragraph pointed me to the solution (thank you buddy!).

Still don't know why/how this can happen, maybe it is related to the order of the installations of Build Tools, SSDT, Visual Studio etc.?

The reason: SSDT was missing in the Build Tools and its version of MSBuild.

What I have done:

Copy the SSDT folder from

C:\Program Files (x86)\Microsoft Visual Studio\2017\Professional\MSBuild\Microsoft\VisualStudio\v15.0

to

C:\Program Files (x86)\Microsoft Visual Studio\2017\BuildTools\MSBuild\Microsoft\VisualStudio\v15.0.

WOW! I finally see a REAL error message:

Now I had to copy SQLDB and SQLEditor (I think SQLCommon already existed in the destination folder, if not it should also be copied) from

C:\Program Files (x86)\Microsoft Visual Studio\2017\Professional\Common7\IDE\Extensions\Microsoft

to

C:\Program Files (x86)\Microsoft Visual Studio\2017\BuildTools\Common7\IDE\Extensions\Microsoft

and BOOM...

And yes, the VS2017 runner from TeamCity is also happy with it.

Answer 2

Seems like the issue with Build Tools and SSDT has been solved now and you don't have to mess with manual copy of targets directories anymore (Visual Studio Build Tools 2017 v15.9.8).

1. Just open the Visual Studio Installed on the build agent server
2. Modify Build Tools installation
3. Select to install the "Data storage and processing tools"
4. Modify

Build should work now.