I am a big fan of Octopus Deploy. This tool helps us to deploy our applications with ease and in a reliable way. In fact, it is so easy that I always forget the few important settings you need to have in place so that Octopus can do its magic.
We use SSIS to synchronise our databases and install those packages with the SSIS deployment template from the Octopus Deploy Library. As we were installing a new server the deployment task failed with this error message:
Connecting to server …
Getting reference to catalog SSISDB
Creating SSIS Catalog …NotSpecified: Exception calling “Create” with “0” argument(s): “Only SysAdmin is allowed to create the catalog of Integration Services. Contact the administrator of the SQL Server Instance to get the necessary permissions.”
How could this be? The database user we defined in the connection string has all the necessary permissions.
Octopus offers multiple ways to run a script. In the SSIS package deployment step runs as user NT AUTHORITY\SYSTEM and this user had no permissions to access the SSISDB. We added the user to the database and gave it the ssis_admin role:
With this little change in place, Octopus deployed our SSIS packages without any problems.