Deploying SSDT During Local and Server Build

4 years ago, I wrote a post about how to deploy VS DB (Visual Studio Database Project) to your local SQL Server during your local build.

As you may already know that a new version of SSDT (SQL Server Data Tool) has been released with VS 2012 to replace the normal VS DB project that was existing with VS 2010 and of course the deployment changed.

For more information and understadning about SSDT, see this post:
What’s New in TFS 2012?- SSDT (SQL Server Data Tool)

Now we have a feature called publish profile which you can put all your setting in that file and use it afterwards to deploy the DB.

This post has 2 sections:

• Deploy and publish SSDT to local SQL Server during local build
• Deploy and publish SSDT to a shared SQL Server during TFS Build

Deploy and publish SSDT to local SQL Server during local build

If  you need to work with SSDT for SQL Server 2014, then you will need to install the last SSDT, it’s not included in VS update 3, download SSDT

1.Using SqlPublishTask of MSBuild
I edit the SSDT project file as the following:

<Target Name="DeployDB" AfterTargets="build" Condition="'$(Configuration)' == 'Local'">
<Message Importance="high" Text="************** Star Deploying DB **************" />
<Message Importance="high" Text="Deploying Project: $(MSBuildProjectDirectory)\$(MSBuildProjectName).sqlproj" />
<Message Importance="high" Text="Deployment Profile: $(MSBuildProjectDirectory)\PublishProfiles\$(MSBuildProjectName).local.publish.xml" />
<MSBuild Projects="$(MSBuildProjectDirectory)\$(MSBuildProjectName).sqlproj" Properties="SqlPublishProfilePath=$(MSBuildProjectDirectory)\PublishProfiles\$(MSBuildProjectName).local.publish.xml" Targets="sqlPublish" />
</Target>

I used some of MSBuild Reserved and Well-Known Properties like $(MSBuildProjectName) and $(MSBuildProjectDirectory), for complete list, click on this link

====================================================================================

2.Using MS Build Command line with SqlPublishTask

msbuild /t:build;publish "C:\TFS Source\Model\Calculator\Calculator.DB\Calculator.DB.sqlproj" /p:SqlPublishProfilePath="C:\TFS Source\Model\Calculator\Calculator.DB\PublishProfiles\Calculator.DB.local.publish.xml"
"$(MSBuildProgramFiles32)\MSBuild\12.0\Bin\msbuild" /t:build;publish "C:\TFS Source\Model\Calculator\Calculator.DB\Calculator.DB.sqlproj" /p:SqlPublishProfilePath="C:\TFS Source\Model\Calculator\Calculator.DB\PublishProfiles\Calculator.DB.local.publish.xml"

• If we run the MS Build from withing the project path we can use relative paths instead of absolute paths
• To use MSBuild without path, we need to open Visual Studio command line otherwise we may having this error “exited with code 9009” which means it couldn’t find the MSBuild.exe”, so I will need to use the full path and this is always the case when working with pre and post build events

====================================================================================

3.Using SqlPackage.exe

SqlPackage.exe is a command line utility that automate some of the DB development tasks

We can call SqlPackage as a post build event for SSDT project as the following:

"$(MSBuildProgramFiles32)\Microsoft SQL Server\110\DAC\bin\sqlpackage" /a:publish /sf:"$(SolutionDir)\$(ProjectName)\$(OutputPath)\Calculator.DB.dacpac" /pr:"$(SolutionDir)\$(ProjectName)\Calculator.DB.local.publish.xml"

The .dacpac file is the output of building sqlproject, as the dll is the output of building a class library
====================================================================================

Deploy and publish SSDT to a shared SQL Server during TFS Build

There are many ways too, the SSDT’s team blog create a great post for all ways, for more info, click on the following link:
SQL Server Database Projects and Team Foundation Build

1.Using SqlPublishTask of MSBuild
I will send some MDBuild arguments in the build definition as the following:

/t:Build;Publish /p:SqlPublishProfilePath=../Calculator.DB\PublishProfiles\Calculator.DB.local.publish.xml

But because my solution has many projects not only SSDT project, which means I build the solution file not the SSDT project file only, this means, the build argument will be sent to each project file in the solution and this gives error with one of the Win Form project as the following:

MSBuild error MSB3021: Unable to copy file C:\Program Files (x86)\MSBuild\12.0\bin\amd64\Microsoft.Common.CurrentVersion.targets (4691): Could not copy the file “exe.manifest” because it was not found.

This error will disappear if we only build SSDT, of course I can use other ways like using SqlPackage.exe with custom build but as this is the way I did on my local build, I prefered to be the same on the Build server, so I customize the build as the following

I used 2 editors:

• Microsoft.TeamFoundation.Build.Controls.BuildProjectListEditor
• Microsoft.TeamFoundation.Build.Controls.StringListEditor

Which makes me be able to select multiple build project and multiple publish profiles, for more info about Build Editors, see the following post:
TFS Build Editors and Build Process Metadata