Automating deployment of SSIS projects
As mentioned in the previous post: Automating building of SSIS projects, I am working on integrating our SSIS
projects into our continuous build/deployment process. In this post, I will cover my implementation of automating
the deployment of SSIS projects.
Our continuous delivery pipeline uses the Octopus Deploy platform which is a
release management; automated deploy and operation runbook platform that consists of a central server and
deployment agents referred to as tentacles.
The tentacles receive the deployment packages, which contains the code being deployed, and runs the deployment scripts.
The server is where all the deployment packages are stored; deployment projects are maintained and the management of the following: infrastructure, users and server.
Octopus deployment projects are defined as steps from one of the following templates types: Built-in, custom or Community Contributed. The template could be defined in one of the following languages: PowerShell, C#, F#, Bash or Python3.
Before I could create my deployment project. I had to extend my automated build by adding another PowerShell script to create deployment packages and publish them to the server.
The sample of the script can be found here: PublishSSIS.ps1
I started my deployment project by using the existing community templates for deploying SSIS ISPACS projects. I found them easy to use to deploy the SSIS project and configure either by directly changing the SSIS parameters or via an environment configuration.
When using the environment configuration, which is how we deploy our SSIS projects, the existing templates copied all the SSIS project and package parameters into the environment configuration and set the values either from SSIS project/package parameter or matching Octopus variable name.
I didn’t like this approach as it made it hard to see and find what configuration was set by Octopus. This was due to a large number of parameters existing in the environment configuration from all the connections defined within our SSIS projects and packages.
I decided to create my own Octopus template, which I publish to the Octopus library called: "SSIS Deploy ispac with Environment", to only create environment references when Octopus variable name, which has to be wrapped with SSIS[], matches the name to a SSIS parameter. For package level parameters the Octopus variable name also has to be prefixed with the package name within []. Any environment references when there are no matching Octopus variable with this pattern are removed.
My template also differs in the following ways:
Will not create the SSIS catalogue if it didn't exist, as I don't want one-time operation included in my template. As well as to reduce the risk of my template not working due to new parameters required for creating SSIS catalogue for future release of SQL Server.
Will always create environment reference for the project being deployed. The name of the environment reference will be the same as the project name being deployed with a defined tag. The default the tag will be the name of the environment that is SSIS package is being deployed to.
I would like to give thanks to the other SSIS templates community contributors as their code helped me to write my own template.
The tentacles receive the deployment packages, which contains the code being deployed, and runs the deployment scripts.
The server is where all the deployment packages are stored; deployment projects are maintained and the management of the following: infrastructure, users and server.
Octopus deployment projects are defined as steps from one of the following templates types: Built-in, custom or Community Contributed. The template could be defined in one of the following languages: PowerShell, C#, F#, Bash or Python3.
Before I could create my deployment project. I had to extend my automated build by adding another PowerShell script to create deployment packages and publish them to the server.
The sample of the script can be found here: PublishSSIS.ps1
I started my deployment project by using the existing community templates for deploying SSIS ISPACS projects. I found them easy to use to deploy the SSIS project and configure either by directly changing the SSIS parameters or via an environment configuration.
When using the environment configuration, which is how we deploy our SSIS projects, the existing templates copied all the SSIS project and package parameters into the environment configuration and set the values either from SSIS project/package parameter or matching Octopus variable name.
I didn’t like this approach as it made it hard to see and find what configuration was set by Octopus. This was due to a large number of parameters existing in the environment configuration from all the connections defined within our SSIS projects and packages.
I decided to create my own Octopus template, which I publish to the Octopus library called: "SSIS Deploy ispac with Environment", to only create environment references when Octopus variable name, which has to be wrapped with SSIS[], matches the name to a SSIS parameter. For package level parameters the Octopus variable name also has to be prefixed with the package name within []. Any environment references when there are no matching Octopus variable with this pattern are removed.
My template also differs in the following ways:
Will not create the SSIS catalogue if it didn't exist, as I don't want one-time operation included in my template. As well as to reduce the risk of my template not working due to new parameters required for creating SSIS catalogue for future release of SQL Server.
Will always create environment reference for the project being deployed. The name of the environment reference will be the same as the project name being deployed with a defined tag. The default the tag will be the name of the environment that is SSIS package is being deployed to.
I would like to give thanks to the other SSIS templates community contributors as their code helped me to write my own template.
Comments