DevOps : Building your SSIS project in VSTS (Part IV)

Introduction

In previous blogposts, I've explained how to build a database project (Part I), how to release a database project (Part II) and how to use pre- and post deployment script (Part III) in an automated build and release process with VSTS. The next step is to examine how to build and release a SSIS project. This blogpost is the result of this exploration.

Building code in a database project is done with MSBuild. It is not possible to use MSBuild for building a SSIS project (as far as I know). The options I've have found so far are :

• Using command line task with devenv.com. 
• Download a custom SSIS task from the marketplace.

I'll examine both ways but will describe the first one in this blogpost. I've used the following sites as an inspiration : http://abelsquidhead.com/index.php/2016/10/06/build-and-deploy-ssis-packages-using-vsts/

Creating a SSIS project in a solution

Now the first step in this demo is creating a SSIS project in my WorldWideImporters (I think I mixed up the name;-) ) solution. The database project is still present. It is the same project as I described in my previous blogposts.

The next step is to build the SSIS project. In the following step the changes are committed and pushed to the Git repository.

The first step is checking whether the build and release is still working and it is. But now we have to change the build process in VSTS. We not only have to build a database project but also the SSIS project now.

Build the SSIS project in VSTS

The first step is changing the VSTS build definition in VSTS. I've made a copy of the build definition and added some tasks.

Variables
I've added two variables for the build configuration and one for the SSIS project. I did not configure the configuration properties of the properties. I just took the defaults.

Build task
The Build task is a command line task in VSTS and there are two version 1.* and 2.*. I took the 1.* version.

This example is based on the "Hosted" agent queue

       
C:\Program Files (x86)\Microsoft Visual Studio 14.0\Common7\IDE\devenv.com
 

Using the "Hosted VS2017" agent has another location of the devenv.com

       
C:\Program Files (x86)\Microsoft Visual Studio\2017\Enterprise\Common7\IDE\devenv.com

These are the arguments I used for the build.

"$(Build.SourcesDirectory)\WorldWideImporters-SSDT\WorldWideImporters-SSDT.sln"  
/rebuild $(BuildConfigurationSSIS) 
/project "$(Build.SourcesDirectory)\SSISWorldWideImporters-SSDT\SSISWorldWideImporters-SSDT.dtproj"

Copy task

Use the copy task again as I described in the database build blogpost.

Publish artifact

Publish the artifact to the Artifact repository.

You can find these artifiacts in the overall result screen of the build. These will be used in the release processstep.

Conclusion

Once you understand the folder structure and using the variables it is easy to write the script to build the SSIS project with Devenv.com.

Regards,

Hennie