Azure DevOps extension for XL Deploy

XL Deploy and Microsoft development process improvement tools are long date friends. XL Deploy started supporting TFS since the 2010 version. Initially, the integration came in form of custom build activities for XAML templates. With TFS 2015, Microsoft introduced a simpler task and script-driven cross-platform build system and XL Deploy followed up with an extension that delivered the custom tasks that will integrate operations in between our build/releases and XL Deploy. The newly introduced extension was named ‘VSTS extension for XL Deploy’ which was published to the VSTS Marketplace so that all the customers can easily integrate it on their systems. You can find more information on the argument on XebiaLabs blog, for example V7.0 Plugin Adds Fine-Grained Control to Microsoft TFS/VSTS Deployments.

Today XebiaLabs released a new version of the above-mentioned extension. The version number is 8.5 and it brings a lot of interesting improvements.

What’s new?

First of all, the name. The extension is renamed to follow up on Microsoft new naming. The name is “Azure DevOps extension for XL Deploy”.

One of the major changes is laying under the hood. Tasks delivered by the extension are no more implemented with PowerShell, instead, they are based on NodeJs. This allows us to run all of them also on cross-platform agents. This, however, will require the agent version 2.117.0 or newer.

There is an improved endpoint definition. You can now test your connection directly from the endpoint definition window. Also, there is better and more precise handling of the self-signed certificate that you can now ignore by selecting “Accept untrusted SSL certificates” flag on the endpoint service.

Also, “Deploy with XL Deploy” task was simplified by letting you specify an advanced search pattern that will look for your deployment archive.

How will this impact the customers using it?

If you are already using ‘VSTS extension for XL Deploy’ extension, you can upgrade it with “Azure DevOps extension for XL Deploy”. Once the upgrade is done, your builds/releases will still continue using the old version of the tasks, until you do not modify them and manually upgrade to the new, version 7, of the task.

In case you encounter any issues with the new version, you can always rollback the task to the previous version.

This is a typical path of the major version update for tasks in Azure DevOps Services, same as many of the out-of-the-box tasks have shown us.

You can find the nex extension here Azure DevOps extension for XL Deploy on Visual Studio Marketplace.

Deploy SSIS packages from TFS/VSTS Build/Release

Automating BI projects, based on Microsoft tooling, was always a bit painful. Personally, all of the things shipped with SSDT seemed a bit unloved from a Microsoft side.

What I would like to show you in this post is how I managed to automate the build and deployment of my Integration Services Projects (.dtproj type). We will build our project in order to get the necessary ispac files, upload this artifact in TFS and then deploy it via a TFS Release.

Let’s start with the build. As you may already know, IS Projects are not based, as most of Visual Studio projects, on MSBuild. This is the first difficulty everyone faces once they start setting a CI/CD pipeline for this type of projects. The only way to build it is to use devenv.com executable that is part of the Visual Studio installation. For that, you will need a custom build task or you’ll need to execute a script that will handle the necessary.

In my case, I made a custom build task for it (which can be found here) and thus my build definition looks like this:

Following are the steps of my build. First I will ‘compile’ the SSIS project, then copy the ispac files that were generated to the Build Staging folder, again I’ll copy my SSIS configuration file (more about the configuration file later) to the same location and at the end, I’ll upload all of the files in the Staging folder to TFS as my build artifact.

Now that my artifact is ready, we can proceed with the deployment in the release.

In order to deploy the SSIS package, I’ll use my own build/release task called Deploy SSIS.

The release steps will look like the following.

As you can see I will start with replacing the placeholders in the configuration file. The configuration file contains some environment dependent values and in the first step, I’ll make sure that those are substituted with the correct values that are coming from the environment variables in the release.
But what is this configuration file I already mentioned a couple of times? It is a file that my deploy task is capable of processing and it contains a list of SSIS environments and variables. There are no other ways of supplying this information, considering that it is not part of the ispacs, and as it was a necessity for my automation to provide them, I came up with a schema from which my Deployment task will pick those up, add them to my SSIS instance and eventually make all of the necessary references. Following is an example of the configuration file.

<?xml version="1.0" encoding="UTF-8" ?>
<environments>
    <environment>
        <name>MyEnv</name>
        <description>My Environments</description>
        <ReferenceOnProjects>
            <Project Name="BusinessDataVault" />
            <Project Name="Configuration" />
        </ReferenceOnProjects>
        <variables>
            <variable>
                <name>CLIENTToDropbox</name>
                <type>Boolean</type>
                <value>1</value>
                <sensitive>false</sensitive>
                <description></description>
            </variable>
            <variable>
                <name>InitialCatalog</name>
                <type>String</type>
                <value>DV</value>
                <sensitive>false</sensitive>
                <description>Initial Catalog</description>
            </variable>
            <variable>
                <name>MaxFilesToLoad</name>
                <type>Int32</type>
                <value>5</value>
                <sensitive>false</sensitive>
                <description>Max Files To Load by dispatcher </description>
            </variable>
        </variables>
    </environment>
</environments>

It represents a set of environments. Each environment needs a name. Once defined the task will proceed to create them in SSIS. Environments must be referenced to projects in order for projects to used them, therefore the ReferenceOnProjects element will allow you to enlist the projects on which you would like to apply these references automatically. Last but not least, a list of variables that do need to be populated for the given environment. Variables will also be automatically referenced in projects if the names are matching with parameters. In the end, for the example in question, you will get the following.

Now that we cleared why do we have the config file and how to set it up, I’ll mention you that it is also checked in the source control as my dtproj and other files. In case you are not a big fan of XML, you can provide the same content in form of JSON file.

Let’s see now what are the necessary parameters for our Deploy SSIS task.

First, the SQL instance on which SSIS is running. That’s an easy one and should be set in the Server name field. You can choose the Authentication type for the connection to be established and it can be the Windows Authentication and SQL Server Authentication. Based on this choice the relevant SQL Connection String that is used to communicate with your server will be set. In case of Windows Authentication, the build agent identity will be used, so make sure that account has the necessary privileges in SQL.

In case you are deploying multiple applications in SSIS, you would like to flag the Share Catalog option because then, the catalog will not be dropped before deploying. Also if you have any other reasons not the drop the catalog on each deployment, this option will do.

SSIS folder Name parameter is self-explanatory. We need to deploy our projects and environments in a folder, that’s the only possible option in SSIS. Thus, we need to choose a name for a folder that we are going to deploy our projects and environments in.

As the last parameter Environment definition file is the location of our configuration file. If not supplied, projects will still be deployed however, no variables will be created and referenced.

I do hope that this is sufficient to get you started with automating your SSIS deployments. If any, don’t hesitate to ask in the comments.

XL Deploy in VSTS/TFS build/release pipelines

Introduction

Circa a year ago I showed you how to use XebiaLabs Team Foundation Server 2015 XL Deploy plugin to create, import and deploy, XL Deploy Deployment Archives, on TFS and VSTS. With a new version of the plugin, that is now renamed into VSTS extension for XL Deploy, XebiaLabs delivers two more build/release tasks. This tasks are supported both by build and release pipelines in VSTS and Team Foundation Server. This will allow us to be more granular and chose to perform only certain operations at each stage, in our pipelines. We can at example choose to create our XL Deploy Deployment Archive during the build and later on, in our release definition, deploy the same artifact in different environments, using XL Deploy.
In the following post, I’ll show you an example on how to achieve this by using our two new tasks, respectively, Package with XL Deploy build task and Deploy with XL Deploy build task.

Building the artifact

As in my previous post, we will keep our Visual Studio Build, however, instead of using the XL Deploy build task, we will add Package With XL Deploy task which you can find in the Package category in the Task Catalog

Once added, we need to point it to the right manifest path, chose if we want the task to set the version of the created package based on the build number and, in our case, Publish the artifact to TFS. In this way once the package is created, it will be uploaded to TFS and associated as an artifact to the current build. At the end the result should be similar to the following.

You can now try your build and at the end, if successful, you should see our DAR package under the build artifacts.

If you can see your artifact uploaded, we can move to the next step.

Deploying from the VSTS Release Management

We are going now to create a new release definition and start with an empty template. As artifact source we are going to indicate a build and select from the relevant drop-down, build definition that we created in our previous step. You also may choose to select a continuous deployment option and a specific build queue for this build.

Once the release definition is created, for your initial Environment, choose Add task and select Deploy with XL Deploy from the task catalog.

This task will allow us to import the DAR package into XL Deploy and trigger the deployment for the desired environment. Bare in mind that it will check if the package is already in XL Deploy and, if so, will skip the import. This means that if you are using it multiple times for different environment definitions, it will behave as efficiently as possible.

Now we need to select the XL Deploy Endpoint, which indicates the instance of XL Deploy we are using for this deployment and fill in the necessary indications about the artifact location and the name of our target environment. For what concerns the XL Deploy Endpoint you can get further information in Add an XL Deploy endpoint in Visual Studio Team Services document. Artifact can be ‘picked up’ from the Server, it means that it will be made available by the release itself, or it can be retrieved from the file share. In our case it will be delivered by the release which will gets it from the associated build.

That’s all! Now we can create a new release and let our task delegate the deployment to XL Deploy.

Conclusion

We saw how can we leverage the new build/release tasks to interact with XL Deploy in different phases of our Build/Release process.

You can find more VSTS specific information at the following link or more information about the VSTS extension for XL Deploy here.