Release SSIS using Azure DevOps

With this new post, I continue to explain how to release SSIS packages using pipelines in Azure DevOps. This helps you achieve a proper CD/CI for your Windows Service projects. Some context is provided by the Microsoft documentation.

Here is the list of posts related to this one:

• Deploying Windows Services using Azure DevOps
• Releasing Windows Services using Azure DevOps
• Release SSIS (SQL Server Integration Services) using Azure DevOps

The source code of this post is available on GitHub.

This post is part of the deployment posts I wrote before, and you can see the link above. I assume the connection with Azure DevOps and the target SQL machine is established using the script Azure DevOps provides. For more details, look at the first post.

Create the artifact for SSIS

First, an SSIS (SQL Server Integration Services) package can be created in Visual Studio. You need to install SQL Server Integration Services Projects 2022 from the Visual Studio Marketplace. Then, the code for the SSIS package is stored in a repository in Azure DevOps.

Now, we can begin the first part of the full pipeline. This involves creating the artifact of the SSIS package. So, start a new pipeline and select at the bottom of the page the option Use classic editor.

After that, you have to select the source from where you read the code. As I said, the repository in my case is in Azure DevOps. So, I select Azure Repos Git.

Now, select the Team project, the Repository and the Default branch. The branch can be changed later if it is needed. Then, click on the Continue button. Then, there is a pipeline template to choose from. I select Empty job because there is no one template for SSIS.

After that, I am redirected to the page where I can design the pipeline. In the Pipeline section, select “Azure Pipelines” as the Agent Pool for the build pipeline. Also, in the Agent Specification, select windows-2019.

Now, just for my reference, I have changed the Display name for the build agent job. This is not required but it helps you to understand what the pipeline does.

The next step is to add a new task to the pipeline. For that, there is a + sign related to the agent. So, click on the + to add a new task.

Add SSIS DevOps Tools

Now, the tasks I have to add are part of the SSIS DevOps Tools, a free package offered by Microsoft. If this package is not installed in your instance of Azure DevOps, you need to install it. You can get it free from the marketplace. So, click on the button Get it free and authorize the installation. Here is the link to this tool in the marketplace.

After the installation of the SSIS DevOps Tools, there are 3 new tasks in the list:

• SSIS Build Task: supports building single or multiple dtproj files in project deployment model or package deployment model.
• SSIS Deploy Task: supports deploying single or multiple ispac files to on-premise SSIS catalog and Azure-SSIS IR, or SSISDeploymentManifest files and their associated files to on-premise or Azure file share.
• SSIS Catalog Configuration Task: supports configuring folder/project/environment of SSIS catalog with a configuration file in JSON format.

Add the SSIS tasks

Now, I can continue with the release of SSIS packages using Azure DevOps. So, I am going to add a new task using the task called SSIS Build. So, select SSIS Build from the list of tasks.

After clicking on the task, I have to click on the button Add.

Now, in the pipeline, I see the new task. This task needs to be configured now.

So, click on the task Build SSIS and see the settings for this task. First, I have to select the Project Path to find the SSIS project to build. Click on the ... to select the project to build.

After clicking on the ..., I have a pop-up window with the content of the repository I chose at the beginning. From this list, I have to choose the file with extension dtproj.

After that, I have to add another task to Publish build artifacts. Again, click on the + in the Agent Job and in the list of tasks, search for publish artifact. Then, click on it to add it to the pipeline.

Now, the Publish Artifact task is added to the Agent Job. Click on it to see the settings. I am going to change the Artifact name. I found quite useful to select a specific name like drop, because in the Release deployment pipeline, I have to select the name of the artifact. If I select a random name or depending from variables like BuildId, it will prevent me to select an artifacts to deploy.

After this change, I can click on Save and run the build pipeline. If everything is set correctly, the pipeline will run successfully. Here is an example of the result of my pipeline.

Set the CD/CI for the pipeline

I want to execute the pipeline now. A new artifact should be created every time there is a change in the branch. So, click on the tab Triggers and then click on the checkbox for Enable continuous integration.

Release pipeline

Now, here is the second part of the pipeline. So far, I have built the artifact, but now I have to deploy the artifact to the SQL Server. For that, click on the Releases under Pipelines. If this is the first pipeline release, you see a page like this one.

If you have already other pipelines, you can add a new release pipeline, click on the button New and then click on New release pipeline.

Now, clicking on that, I see a page like the following page. From this page, click on Empty job.

I want to deploy the SSIS package on different environments. Therefore, I will name every stage with the name of the environment. For example, the first stage is DEV.

Now, I have to configure the artifact I want to deploy. For that, I click on Add an artifact. Remember the drop file I created before?

Now, I have to select the source. Click on the dropdown list and select the artifact I want to deploy.

After that, the other fields are automatically filled. Here is an example of the settings.

Now, click on the button at the bottom with the Add text. Next, I am going to configure the tasks in the first stage. For that, click on the link under the name of the the stage to configure the stage.

First, again, I am going to change the Display name of the Agent job. This is not necessary, but it helps me to understand what the pipeline is doing.

Also, select from the Agent pool in the Agent selection, the Default option. Now, I have to add a new task for this Agent job. Click on the + to do so.

The next step is to add the task called SSIS Deploy that is part of the SSIS DevOps Tools. If I move the mouse on this task, I see the option to Add the task to the agent.

Now, I have to configure the task. So, I have to click on it.

The setting for this task are like in the following screenshot.

Now, I have to select the Source path for the package I want to deploy to the SQL Server. For that, click on the ... to open a pop-up window with the list of packages in the folder. Select a file with.ispac extension.

After that, in the dropdown list for Destination type, choose SSIS.

Add a variable for the SQL server name

Now, because every environment has a different SQL Server name, I will add a variable for it. So, click on the tab Variables, add a variable for the server name and then set the scope to DEV.

Go back to Tasks and then set the Destination Server to the variable $(ServerName). Now, set the Destination Path to the destination folder where the source file will be deployed. For example:

1. /SSISDB/<folderName>/[<projectName>]
2. \\<machineName>\<shareFolderName>\<optionalSubFolderName>

Set the Authentication Type to Windows Authentication. This is because I run the Azure DevOps script to connect the SQL machine to Azure DevOps.

Set the Authentication Type to Windows Authentication. Now, the settings look like the following screenshot.

You can rename the pipeline and then Save.

Create a new release

Now, the last action is to start the deployment. For that, I click the Create release at the top right of the screen.

When I click, a drawer opens with the settings for this release. I can check the target environment, and I can add a description for this release.

Now, everything is fine for me. So, I can click on the Create button. If the release pipeline is configured correctly, in the SQL Server I can see the package.

Wrap up

I hope this post can help you in setting a new release for SSIS packages using Azure DevOps. I have created many pipelines for that for different environments. Please keep in touch if you have any comments or questions about it.