This post follows on the heels of building an SSIS Project.
Continuous Integration leaves us with a working set of code, but it does not necessarily deploy it. When we get into serious automated deployment, we need to start looking at Continuous Delivery (CD) servers. This could be Release Management from Microsoft, Octopus Deploy, Maven or one of many others. There will be no hooks for easy SSIS deployment in any of these so we will script this out in powershell.
Here is an example script, which I will follow with some explanation:
There are two steps that happen here:
- Create an SSIS folder to deploy to
- Deploy all of the projects from a staging directory to the the SSIS folder
We use the Microsoft.SqlServer.Management.IntegrationServices namespace for deployment. The beginning of the script declares a function for creating the folder for cleaner code. All the comments would be stripped out of the final script as well. Once the catalog folder is created, we can use the DeployProject method on the folder. This method accepts the name of the project (without the extension) and the contents of the file in a byte array.
Since the deploy project output does not give the deployment server a very clear outcome, we check the status of the results and throw an error if there was an issue.
Next up, Creating an Environment…