Build and Deploy SSIS with Azure DevOps Pipelines

SQL Server Integration Services (SSIS) projects can be created to perform ETL (Extract Transform and Load) operations. As Implementing of Continuous Delivery becoming a mandatory aspect of any type of software project it is vital for SSIS projects to be able to implement CI/CD. With the availability of the extension “SSIS Build & Deploy” in Marketplace for Azure DevOps, the CI/CD implementation for SSIS has become straightforward to implement. Let’s look at a sample to understand how to get CI/CD implemented for SSIS project with Azure DevOps.

You can create SSIS projects in Visual Studio and define project level parameters as shown below (More information on creating SSIS projects with VS 2017 can be found here).

A simple sample such as copying data from one database table to another database table can be implemented as a trial. To build and deploy SSIS project with Azure Pipelines first get the extension “SSIS Build & Deploy” installed to Azure DevOps. Two important tasks getting added with this extension. One for builds SSIS projects and another for deploying SSIS project.

Building SSIS project with Azure DevOps

Create a new build definition and add SSIS Build task got added with the extension “SSIS Build & Deploy”. You should provide the path to the SSIS project instead of the Visual Studio solution. Command line switch should be Build and you have to specify the Visual Studio version to use.

Once build you can copy the *.ispac file (deployable SSIS project) for publish as build output.

Then create a release definition and add using the SSIS build as artifact source.Define the required variables for the parameters in SSIS project as well as variables to keep target database server information.

In the release environment add SSIS deploy task that is installed with the extension “SSIS Build & Deploy”.  Provide the path to *.ispac file to deploy.

The parameters defined in the SSIS project can be replaced with the variable values defined in the release definition. You have to specify the parameter names and variables in json file format as in below example.

{
   "parameters":[
      {
	     "Name":  "SourceDatabase",
	     "Value": "$(Param.SourceDatabase)"
      },
      {
	     "Name":  "SourceSQLUser",
	     "Value": "$(Param.SourceSQLUser)"
      },
      {
	     "Name":  "SourceSQLUserPwd",
	     "Value": "$(Param.SourceSQLUserPwd)"
      },
      {
	     "Name":  "SourceSQLServer",
	     "Value": "$(Param.SourceSQLServer)"
      },
      {
	     "Name":  "TargetDatabase",
	     "Value": "$(Param.TargetDatabase)"
      },
      {
	     "Name":  "TargetSQLUser",
	     "Value": "$(Param.TargetSQLUser)"
      },
      {
	     "Name":  "TargetSQLUserPwd",
	     "Value": "$(Param.TargetSQLUserPwd)"
      },
      {
	     "Name":  "TargetSQLServer",
	     "Value": "$(Param.TargetSQLServer)"
      }
   ]
}

Deployment of SSIS packages is possible only with Integrated windows authentication and the user running the build/release agent should have sufficient permissions in SQL server to do the SSIS project deployment. The deploy task currently has an issue and only way for you to specify the connection string as custom connection string. the issue

[error]Task_InternalError Exception calling ".ctor" with "1" argument(s): "Property Login was not set."

is discussed here. You can use below connection string to avoid the issue with your preferred variable name for SQL Server.

Data Source=$(SSIS.SQLServer);Initial Catalog=master;Integrated Security=SSPI;

With these setting you would be able to get the SSIS project successfully to the target server.

Parameter values set in project will be replaced with the variable values defined in the release template (Notice the TargetDatabase was set as SampleDB in the SSIS project but it got replaced with the value TargetDB defined as variable “Param.TargetDatabase” in the release definition).