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.

23 thoughts on “Deploy SSIS packages from TFS/VSTS Build/Release”
  1. Very good article. Do you recommend to create a specific Active Directory account for this ? (i.e. SSIS_DEPLOY_ADMIN) And what are the rights this account should have (SQL Server and Windows) ?

    1. Thank you Pierre.

      As the task is running with your agent identity in case of Windows Authentication, that account needs to have sufficient privileges on SQL to deploy your packages. Otherwise you can use SQL Server Authentication type and specify an account of your choice, however your SQL needs to be set to handle the authentication and again the same account needs to be able to publish your package.

  2. Trying out the code, and works with Windows Authentication, but I get an error with SQL authentication …
    Exception calling “DeployProject” with “2” argument(s): “The operation cannot be started by an account that uses SQL Server Authentication. Start the operation with an account that uses Windows Authentication.”

  3. Hi,
    Thanks for the article.
    Could you please let me know where can i find the ISPAC files.
    i tried t use Copy files task but it says there are no files to copy.
    Could you please help me in this.

  4. Hi there. I’m looking for a way to automate the running of SQL scripts within TFS for our BI team so that involvement from a DBA isn’t necessary. This looks to only be a way to automate the deployment of SSIS packages to servers is that correct?

  5. Hi there. I’m looking for a way to automate the running of SQL scripts within TFS for our BI team so that involvement from a DBA isn’t necessary. This looks to only be a way to automate the deployment of SSIS packages to servers is that correct?

  6. Can you tell me about how long a typical deploy of an ispac file with one project takes for you? I am deploying using a Powershell script calling the deployProject commandlet and it is taking about 2 minutes per ispac file which has one project. This seems like an excessively long time. If it is, then I need to troubleshoot why it is taking so long. I have not tried your utility yet, but might give it a try if you have better performance.

    1. Deployment performance are obviously bound to the infrastructure on which SQL is deployed. However, it ain’t fast even for the small deployments. The libraries and calls used tend to be ‘slow-ish’ compared to the size of the package that is deployed. Still however I find it reasonable. Can recall precisely how long it was taking for the last project I worked on, but it was under 10 minutes for sure, in the situation where we had cca 50 packages.

  7. Hi Mario, thanks for taking the effort to write and release the solution. Quick question, is it possible to run this solution on one machine and deploy the ispac to a different machine? This saves me having to install the deployment components on every machine.

    thanks

    1. Hi Jason, the task is expecting that the necessary dependencies will be present on the build agent machine. At the moment there is no other way to make it working (like executing the necessary on another machine).

  8. Hello Mario,
    thank you for post.
    Based on the last build task: “Publish ispac artifact”, you publish that on an artifact location right? In my case was Upload ‘D:\a\1\a’ to file container: ‘#/3752292/drop’
    What I missing is how this is picked up from the deploy task “Deploy ISPACs”? what the path to ispac file field should be?

    1. As one of the last build steps, I’m using the task Publish Build Artifact task and I’m choosing as Artifact publish location -> Azure Pipelines (name may vary on TFS). This means that in my release I have Release artifacts sources pointing to the build so that all of the uploaded artifacts are made available during the release. In case you are using a file share to store your artifact, you may use a step in your release to do this, like, Download artifacts from a file share. The path in my case will be $(System.DefaultWorkingDirectory)\[source alias]. You can read more about the Release artifacts and sources here https://docs.microsoft.com/en-us/azure/devops/pipelines/release/artifacts?view=azure-devops. Does this answer your question?

      1. Thank you for your reply.
        Yes, you point me to the right direction.By mistake I used the wrong artifact.
        I can’t understand the use of “Replace tokens in “**\*.xml” task. Could you please describe that more or add another screenshot (if possible)?
        If we add/edit manually the environment variables on each server, could we bypass the “Replace tokens” task, as well as, the environment definition file?
        Thank you in advance

        1. Indeed some of the values of the environment variables were obviously environment-specific, thus they were set during the deployment for that given environment.
          You can omit the environment configuration file in case you are not using environment variables in SSIS. If you add them manually though, you will also need to take care of referencing them, but to be honest I can’t recall if that is always necessary or only in certain cases.

  9. Hi Mario,

    I am able to build and publish an .ispac. My release definition, which uses your ISPAC deploy task, is able to access the .ispac in the drop folder. When I create a new release I get the following error. It says it can’t load SQl Version 13, which is SQL 2016. We have SQL 2014 installed on the server currently. Is there a way to configure the task to use SQL 2014 instead?

    2019-09-05T16:33:33.7484369Z ##[error]Could not load file or assembly ‘Microsoft.SqlServer.Dmf.Common, Version=13.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91’ or one of its dependencies. The system cannot find the file specified.

    Thanks in advance.

    1. Hi Jonathan,

      as already mentioned in the comments, the execution of the task is happening on your build agent. This means that all of the logic behind it is executed there, thus the necessary dependencies do need to be present on your build agent. From the above case, I may suspect you are missing some libraries on your agent, more than the issue with the version itself. A quick trick would be to install the SSDT on your build server in order to deliver all of the necessary libraries, or if on Azure DevOps, try the same with a hosted windows agent.

      Mario

  10. I found your extension for automating the deployment of SSIS packages from Azure DevOps to our environments. We built a self hosted agent and installed the necessary software/libraries/management packs, etc to successfully build the projects. The deployment task works successfully, however I am having difficulty with the configuration after deployment. We do not use environments within our packages, as we have a separate environment for each stage of the process (DEV/QA/UAT/PROD) to which everyone is not granted access. Our DevOps teams handle the deployment and would like the ability to configure the packages directly after deployment. Is that possible with your scripts/build tasks? If so, would you share what changes need to be made to allow the packages to be individually configured directly after deployment?

    Thanks

Leave a Reply

Your email address will not be published. Required fields are marked *