Jonathon

Jonathon Eve-O'Connor's Blog

Continuous Integration With TeamCity – Automating SSIS Build and Deployment

In this blog article I will be detailing how I use TeamCity in order to automate the building and deployment of SSIS projects using the project deployment model. This is a little more complicated than doing a CI build for database projects or other project types as MSBuild will not be able to build or deploy these without a little work.

Prerequisites

This blog assumes the reader has first followed or is familiar with the steps outlined in the previous blogs in this series. These are available from:

http://blogs.adatis.co.uk/jonathoneveoconnor/post/getting-started-continuous-integration-team-city-installation-and-setup
http://blogs.adatis.co.uk/jonathoneveoconnor/post/configuring-teamcity-to-connect-to-visual-studio
http://blogs.adatis.co.uk/jonathoneveoconnor/post/using-teamcity-to-automate-building-and-deployment-of-database-projects

As per the last blog Visual Studio should be installed on the build server, as well as the Business Intelligence Data tools extensions for both Business Intelligence and Database projects. Team City will need to be configured and setup to connect to TFS.

Adding the .dll

There is a dll released by a kind member of the community we will use to enable MSBuild to build/deploy the SSIS projects. The dll is called Microsoft.SqlServer.IntegrationServices.Build before you can enable CI for SSIS projects you will need to get the source and build the dll.The source for the dll is available as part of the ‘Microsoft SQL Server Community Samples: Integration Services’ project which is currently hosted at the following url: http://sqlsrvintegrationsrv.codeplex.com/ 

Once you have downloaded the source from the link you need to open the project located in the relative path ‘\main\SSISMSBuild\Project\Microsoft.SqlServer.IntegrationServices.Build.csproj’

The project may build straight out of the box, however depending on your environment you may need to amend the references so that they are suitable for the environment. I found that because I am running SQL 2014 and visual Studio 2013 a few of the references needed a few changes in order to get everything running smoothly. Once you have added the referenes you should see something like the below (no warnings):

image

I added a file for the strongly named key (key.snk), set the configuration to release and built the project by right clicking the solution and selecting ‘Build’. If all has gone well you should see something like the following:

image

Following this you need to take the new dll from the ‘\bin\Release folder. For the purposes of this blog I placed the file in the path ‘C:\Program Files (x86)\Microsoft Visual Studio 12.0\Common7\IDE\PrivateAssemblies’ however, you may wish to change this depending upon the environment you are working in and the Visual Studio version which is installed.

Configuring Project Settings

You need to add two xml files to your main project, this may not be required if you only have one SSIS project within your solution, however for larger projects I have found this provides a simple method of maintaining the solution and easily adding new projects to the project for CI deployment.

SSISBuild.proj sample file

 

<?xml version="1.0" encoding="Windows-1252"?> <Project xmlns="http://schemas.microsoft.com/developer/msbuild/2003" DefaultTargets="SSISBuild"> <UsingTask TaskName="DeploymentFileCompilerTask" AssemblyFile="C:\Program Files (x86)\Microsoft Visual Studio 12.0\Common7\IDE\PrivateAssemblies\Microsoft.SqlServer.IntegrationServices.Build.dll" /> <!--note that the path to the assembly file referenced above is the one we have just added, the path may need to be amended if running in a different environment.--> <ItemGroup> <SSISProjPath Include="./CI.SSAS/CI.SSIS.dtproj"/> <!--contains the path of the SSIS project to be built--> </ItemGroup> <!-- Add all additional SSIS projects in like this: <ItemGroup> <SSISProjPath Include="./CI.SSAS/CI.SSIS.dtproj"/> </ItemGroup> --> <Target Name="SSISBuild"> <Message Text="**************Building SSIS project: @(SSISProjPath) for configuration: $(CONFIGURATION)**************" /> <DeploymentFileCompilerTask InputProject="@(SSISProjPath)" Configuration="$(CONFIGURATION)" ProtectionLevel="DontSaveSensitive"> </DeploymentFileCompilerTask> </Target> </Project>

 

SSISDeploy.proj sample file

 

<?xml version="1.0" encoding="Windows-1252"?> <Project xmlns="http://schemas.microsoft.com/developer/msbuild/2003" DefaultTargets="SSISBuild;SSISDeploy"> <!--As before the path below may need to be amended depending upon the environment--> <UsingTask TaskName="DeploymentFileCompilerTask" AssemblyFile="C:\Program Files (x86)\Microsoft Visual Studio 12.0\Common7\IDE\PrivateAssemblies\Microsoft.SqlServer.IntegrationServices.Build.dll" /> <ItemGroup> <SSISProjPath Include="DataWarehouse"> <!--the top level name for the project in the SSIS catalog, amend as required--> <DeploymentFile>.\CI.SSIS\bin\Development\Project1.ispac</DeploymentFile> <!--The path to the file which is to be deployed, must match the name property configured in VS for the project.--> </SSISProjPath> <!-- Add new projcts in here as required <SSISProjPath Include="DataWarehouse"> <DeploymentFile>.\ETL.MasterData\bin\Development\MasterData.ispac</DeploymentFile> </SSISProjPath> --> </ItemGroup> <UsingTask TaskName="DeployProjectToCatalogTask" AssemblyFile="C:\Program Files (x86)\Microsoft Visual Studio 12.0\Common7\IDE\PrivateAssemblies\Microsoft.SqlServer.IntegrationServices.Build.dll" /> <Target Name="SSISDeploy"> <DeployProjectToCatalogTask DeploymentFile="%(SSISProjPath.DeploymentFile)" Instance="$(SSISServer)" Folder="%(SSISProjPath.Identity)" CreateFolder="true" <!-- Add additional variables (Catalog and Environment)for new version of IntegrationServices.Build.dll if working with an older version these may not be recognised --> Catalog="SSISDB" Environment="Development"/> </Target> </Project>
The files should be added at the top of the solution which should give something like this:

 

image

Right click the relevant SSIS project and set the Name property to the value you would like to see when the Project is deployed to the SSIS catalog.

image

Team City Configuration

Add a new Build configuration eg ‘SSIS Build and Deploy’ to the project.

image

Attach the TFS root as per the previous blog In the version control settings tab.

Add a new build step ‘Build SSIS Projects’. Depending on your environment and the Visual Studio version installed you may need to use a different version of MSBuild

image

You will notice that in the command line parameters here the value of ‘/p:SSISServer=Development

This setting should be set to match a suitable configuration in your project configuration in Visual Studio.
In this example I have used development.

image

Add another step ‘Deploy SSIS projects’.

image 

Ensure that the command line parameter is of the format: ‘/p:SSISServer=YourDeploymentServerName

Upon completion the setup build steps should look like this:

image

When you view the project in the main Team City ‘Projects’ section you should see the following if all is well:

 

 image

Checking the SSIS catalog we can see that upon check in the project has automatically been deployed to the target server.

image

See the next blog at: http://blogs.adatis.co.uk/jonathoneveoconnor/image.axd?picture=archive/2014/10/07/continuous-integration-with-teamcity-automating-ssas-build-and-deployment.aspx  for details of how to set up automated deployment of SSAS projects.

Comments (4) -

  • Paul Randall

    5/26/2016 7:00:23 AM | Reply

    May I say that this is a fabulous article and it does work. Our situation is slightly different in that we use TFS 2015.

    I have added the code as per above to our project and it indeed builds, but this is where I am in need of some clarification.

    In the SSISBuild.proj file, I have change the SSISProjPath to the project I have, I am not sure what the $(CONFIGURATION) setting is for. Also when I click build, is a bin folder populated in some way?

    • Jon O'Connor

      1/18/2017 6:18:25 PM | Reply

      Hi Paul,

      Apologies for the delay in getting back to you as I realise this comment has now been here for some time.

      Just to let you know the configuration parameter value is passed to the script from Team City. I've updated the blog to add a little section on this.

      You will likely find that the default Development configuration should suffice for this, however it is worth checking that the relevant SSIS packages are configured to build.

      To answer your question about the build, yes. If this procedure is working as I would expect then in the build agent work folder (this will be something like TeamCity\buildAgent\GUID\Dev\IS Project Name\bin\Development\)  for team city, you should see that there will be an .ispac file for the relevant package.

      I hope that this helps make things clearer, if you have any further questions on anything please let me know.

      Kind regards

      Jon

  • Meera

    1/12/2017 8:25:32 PM | Reply

    Hi
    i am trying  to deploy ssis package using teamcity
    i had downloaded your code and try to figure it out.
    some of the dll are missing and i didnt even find them.
    using Microsoft.DataTransformationServices.Project;
    using Microsoft.DataTransformationServices.Project.ComponentModel;
    using Microsoft.DataTransformationServices.Project.Serialization;
    using Microsoft.DataWarehouse.VsIntegration.Shell.Project.Configuration;

    • Jon O'Connor

      1/18/2017 6:10:47 PM | Reply

      Hi Meera,

      Thanks for your query. In order to be able to build the project you will need to update the references for these files within the project.

      The files you mention are installed with SQL Server, this should first be installed on he server and then you need to update the project references to point at the relevant files.

      Hope that this helps. If you have any questions please let me know.

      Kind regards

      Jon

Loading