To show the possibilities with continuous integration with SQL Server development, I decided to spin up a virtual machine with Visual Studio 2013, Team Foundation Server 2013, and SQL Server 2014. While setting it up, I ran into an error that I thought I’d share here.
If you want to know more about how to set up continuous integration for SQL Server development, Jamie Thomson (blog | twitter) wrote a very good blog post about continuous deployment of SSDT database projects to Windows Azure using Team Foundation Service. This approach can also be used for on premise build servers using Team Foundation Server, which is what I was looking for.
With Visual Studio 2013 you can no longer download SQL Server Data Tool separately, as SQL Server tooling is included. You will need to use either one paid versions of Visual Studio or one of the free express editions.
If you wish to deploy to SQL Server 2014, you also need to update the SQL Server tooling from within Visual Studio.
While I was trying to set up a build server, I ran into this error:
C:\Builds\3\MyProject\AdventureWorks Test Server Build\Sources\Main\AdventureWorks\AdventureWorks\AdventureWorks.sqlproj (63): The imported project "C:\Program Files (x86)\MSBuild\Microsoft\VisualStudio\v11.0\SSDT\ Microsoft.Data.Tools.Schema.SqlTasks.targets" was not found. Confirm that the path in the <Import> declaration is correct, and that the file exists on disk.
In my .sqlproj file, the Import declaration showed:
<Import Project="$(MSBuildExtensionsPath)\Microsoft\VisualStudio\ v$(VisualStudioVersion)\SSDT\Microsoft.Data.Tools.Schema.SqlTasks.targets" />
C:\Program Files (x86)\MSBuild\Microsoft\VisualStudio\v11.0\SSDT\ path did not exist, but the
C:\Program Files (x86)\MSBuild\Microsoft\VisualStudio\v12.0\SSDT\ did.
It looked like an old MSBuild version was being used, so the solution I found was to override the variable in the build definition to use the newest version.
This is what I did:
- Find build definition under Builds in the Team Explorer
- Click Edit build definition
- Click Process
- Click Advanced
/p:VisualStudioVersion=12.0under MSBuild arguments
My build arguments ended up looking like this, when adding the build, publish and SqlPublishProfilePath arguments:
/t:Build /t:Publish /p:SqlPublishProfilePath=AdventureWorks.Test.publish.xml /p:VisualStudioVersion=12.0
After that, the build worked just fine.