Wait, didn't I post this already several weeks ago? Well, almost. A few weeks ago, I showed how to set up a build agent using devenv.com. Unfortunately, I ran into some problems like failing builds not reporting failure and SSAS Tabular projects not building correctly[ref]There's also some mentions about SSDT 2015 projects not being supported, but my SSDT 2015 solutions containing exclusively SSDT 2015 projects didn't raise any problems at all[/ref]. However, it turns out to be pretty easy to build almost everything[ref]except SSRS, which is technically a copy-only build, and SSAS Multidimensional, which I don't use but if you want to vote for it here[/ref] using MSBuild. Here's how.

The solution I want to build in VSTS (using MSBuild) consists out of the following project types:

• SSRS
• SQL Server
• SSAS Tabular
• SSIS

SSRS

I haven't got a running SSRS build yet. I don't need to: the build artifacts resulting from a SSRS build are exactly the same as the "source files". So why not just copy the source files? However, I do have some interesting scripts to automate SSRS deployments - if you're interested just PM me (or wait until I post something about these scripts).

SQL Server; SSAS Tabular

When calling the MSBuild task from within VSTS (Visual Studio Team Services), SQL Server as well as SSAS Tabular already builds out of the box.

SSIS

SSIS does NOT build out of the box with MSBuild. However, you can build a library that does. These are the steps you need to take - it's actually pretty easy:

1. Download the source of SSISMSBuild
2. Open the project, fix the references
3. Add a new self-signed key
4. Build
5. Include libraries in source control:
6. Create a .proj-file that calls on the SSISMSBuild library
7. Add a separate MSBuild task in VSTS building all SSIS projects

Download the source of SSISMSBuild

You can do so here: http://sqlsrvintegrationsrv.codeplex.com/SourceControl/latest.

Remember to check if no files are read-only before continuing!

Fix references

Open the .csproj file in Visual Studio. Depending on the version of SQL Server installed on your machine, libraries might reside on another place. Add the missing libraries in each project via "Add Reference..."

I needed to fix three references before I could build the library. In my case, all were located at C:\Program Files (x86)\Microsoft SQL Server\130\Tools\Binn\ManagementStudio:

• Microsoft.AnalysisServices.Project
• Microsoft.DataTransformationServices.VsIntegration
• Microsoft.DataWarehouse.VsIntegration

After fixing the references, you need to change the framework of the C# project accordingly: by default, it's set to .Net 4, while my SQL Server 2016 libraries are built against .Net 4.5. You can do so by opening the project properties, and changing the "Target Framework" setting under the tab "Application":

Add a new self-signed key

As you might notice, there's a missing "key.snk" file in the project.

You need to create a new key to sign the library. The easiest way is opening the project properties, click "signing", then choose to create a new key name file:

Give it any name you like, choose a password and off you go. The key is now called "key.pfx" instead of "key.snk", and you can delete the project reference to the non-existing "key.snk" file.

Build

Well, I expect you to know how to do that.

Include libraries in source control

The project file building your SSIS projects will need to reference some of the dll files created by the build. You'll need these five at least:

• Microsoft.AnalysisServices.Project.dll
• Microsoft.DataTransformationServices.VsIntegration.dll
• Microsoft.DataWarehouse.dll
• Microsoft.DataWarehouse.VsIntegration.dll
• Microsoft.SqlServer.IntegrationServices.Build.dll

Group them in one folder, and add them to source control. I've chosen to bring them into the folder $/(Team Name)/CI/SqlServer.IntegrationServices.Build/.

Create a .proj file

We won't let MSBuild handle with the .dtproj files directly. Instead, we let MSBuild handle a generic ".proj" file that accepts a reference to the '.dtproj' file as an argument and tells MSBuild how to handle the '.dtproj' files.

Below is my personal version - note that

1. I've used several placeholders here, so I need only one MSBuild-proj-file for my +/- thirteen SSIS projects
2. I'm placing this file inside my solution folder. This means every SSIS project has its own subfolder below the '.dtproj' is located

<?xml version="1.0" encoding="Windows-1252"?>
<Project  xmlns="http://schemas.microsoft.com/developer/msbuild/2003"
         DefaultTargets="SSISBuild;SSISDeploy">
  <!--Requires a property called $(SSISProj) to be defined when this script is called-->
  <UsingTask TaskName="DeploymentFileCompilerTask" AssemblyFile="..\CI\SqlServer.IntegrationServices.Build\Microsoft.SqlServer.IntegrationServices.Build.dll" />
  <Target Name="SSISBuild" Condition="'$(SSISProj)' != ''">
    <PropertyGroup>
      <SSISProjPath>$(SSISProj)\$(SSISProj).dtproj</SSISProjPath>
    </PropertyGroup>
    <Message Text="**************Building SSIS project: $(SSISProjPath) for configuration: $(CONFIGURATION)**************" />
    <DeploymentFileCompilerTask
     InputProject="$(SSISProjPath)"
     Configuration="$(CONFIGURATION)"
     ProtectionLevel="DontSaveSensitive">
    </DeploymentFileCompilerTask>
  </Target>
 
 
 
  <UsingTask TaskName="DeployProjectToCatalogTask" AssemblyFile="..\CI\SqlServer.IntegrationServices.Build\Microsoft.SqlServer.IntegrationServices.Build.dll" />
  <Target Name="SSISDeploy" Condition="'$(SSISProj)' != ''">
    <Message Text="**************Publishing SSIS project: $(SSISProj) to: $(SSISServer) to folder: $(PROJECTNAME)**************" />
    <PropertyGroup>
      <ISPac>$(SSISProj)\bin\$(CONFIGURATION)\$(SSISProj).ispac</ISPac>
    </PropertyGroup>
    <DeployProjectToCatalogTask
         DeploymentFile="$(ISPac)"
         Instance="$(SSISServer)"
         Folder="$(PROJECTNAME)"
         CreateFolder="true"/>
  </Target>
</Project>

Add a separate MSBuild task to build all SSIS packages

In my build definition, I've included two steps for building:

1. The default Visual Studio Solution build
2. The custom SSIS build, executed via PowerShell

The default VS Solution Build step uses MSBuild behind the scenes, so there's no devenv.com-magic going on here. It will build SQL Server and SSAS Tabular out-of-the-box, but won't build SSIS.

The custom SSIS build will solely build the SSIS projects. I'm executing MSBuild here via PowerShell, so I only need one task for all SSIS projects (even new ones currently not included)

Here's the PowerShell script[ref]Notice that MSBuild on its own still doesn't support the SSIS projects (aka .dtproj files). It just uses another build definition which refers to a library that helps building the .dtproj files![/ref]

Get-ChildItem -Filter *.dtproj -Recurse | 
Where-Object { -Not ($_.FullName -match "obj") } |
ForEach-Object {
  &"${env:ProgramFiles(x86)}\MSBuild\14.0\Bin\MSBuild.exe" /target:SSISBuild /property:SSISProj=$($_.BaseName),Configuration=${env:BuildConfiguration} "${env:BUILD_SOURCESDIRECTORY}\Dev\SSISBuild.proj"
}

One more thing: SSAS Tabular unattended deployment

Above, I've shown you how to do an automated (unattended) build for SQL Server, SSAS and SSIS. This will create build artifacts which you can include in an (automated? unattended?) deployment step, bringing you one step closer to Continuous Integration.

However, in order to do an unattended SSAS Tabular deployment, you'll need not only a .asdatabase, but also three other files:

• .configsettings
• .deploymentoptions
• .deploymenttargets

These three tell for example about the data source settings, impersonation, processing immediately after deployment, retaining security roles or not, and targets. You can easily generate them by running the ASDeployment wizard in output mode answer mode.

Be aware that every environment and use case will result in a different set of desired settings for deployment! Therefor, I've included for all desirable scenarios (D, T, A, P, UAT, etc.) a folder describing that scenario. I've placed them inside the aforementioned 'CI' folder, and copy them as build artifacts. As a result, my deployment agents will always have all possible scenarios at hand for automated deployments[ref]I also have included nunit tests as a build artifact, by the way - but that's another story[/ref].