Centric connect.engage.succeed

ALM and BI: first steps

Geschreven door Hans Oude Middendorp - 03 november 2016

Hans Oude Middendorp
Recently, I worked on a Master Data Management project in which we developed an ETL tool driven by SharePoint lists.

During the project, a number of BI-related activities were performed, including:

  • generation and deployment of database changes, based on the definitions in the SharePoint lists;
  • building an ETL with SSIS.

Since we worked on this project with various colleagues, we used Azure DevOps On-Prem as our source repository.

In a previous blog, I discussed the possibility of executing SQL scripts during a release management process. In this blog, I will briefly discuss how we build and deploy SSIS packages.

Laptop with Application Lifecycle Management Building an SSIS deployment package

SSIS deployment packages (.ispac) cannot be built on a Azure DevOps hosted build server, because there are some extra requirements:

  • Visual Studio 2013;
  • Microsoft SQL Server Data Tools 2013;
  • Microsoft SQL Server Data Tools – Business Intelligence for Visual Studio 2013.

You have to create a build server that meets the above requirements and add it to an agent pool before it can be used to build the packages.

In order to build SSIS deployment packages, the “Visual Studio Build” or “MSBuild” tasks are not feasible. Instead, you have to execute “devenv.exe” using parameters. We will therefore use a command line task with the following parameters:

  • Tool: “C:\Program Files (x86)\Microsoft Visual Studio 12.0\Common7\IDE\devenv.exe”;
  • Arguments: .\solution.sln /build $(BuildConfiguration).

(Please note the quotes around the tool parameter - without the quotes, it will not work.)

One disadvantage of this method is that you have to specify the exact location of “devenv.exe”. However, as mentioned in the blog post above, creating custom tasks in the Azure DevOps or Azure DevOps On-Prem build and release management system is not rocket science. A custom task for building a SSIS deployment package will remove this disadvantage.

After the build, the next step is to push the generated .ispac files to the artifact staging directory:

The last step of the build process is to push the artifacts in the artifact staging directory to the drop location:

The build’s artifacts now contain all of the generated .ispac files.

Deploying an SSIS deployment package

More information about deploying SSIS packages with Azure DevOps On-Prem is available here. From this blog post, I am choosing the “ISDeploymentWizard” method to do the job.

This wizard can be run with various parameters in order to publish the .ispac to the desired location on the SSIS server:

Again, we have to use a command line task in order to execute the wizard. The parameters are as follows:

  • Tool: "C:\Program Files\Microsoft SQL Server\120\DTS\Binn\isdeploymentwizard.exe";
  • Arguments: /S+ /ST:File /SP:".\path to .ispac" /DS:server name /DP:desired location of .ispac in SSIS DB

Example of the arguments variable:

/S+ /ST:File /SP:".\SSIS CI Build\drop\CleansingUtility\bin\development\CleansingUtility.ispac" /DS:database server /DP:/SSISDB/QA/CleansingUtility

The main disadvantage of using this method for this step is that the exact location of “ISDeploymentWizard.exe” must be specified. In this case, too, a custom task can be created in order to deploy SSIS packages to an SSIS server.

Another important note: the build and release management agent must run under the credentials of a user who has sufficient rights on the database server to deploy SSIS deployment packages. When defining a custom task for deploying SSIS packages, I would also consider providing the credentials of a user that has sufficient rights on the SSIS server. This might be a good topic for a later blog post…

Craft Expert Hans Oude Middendorp  is part of the ALM team within Craft, the development programme for IT professionals (powered by Centric). If you would like to follow his blog,sign up for Craft updates.


Schrijf een reactie
  • Captcha image
  • Verzenden