SSIS Deployments

Pre-requisites

Linux will require permissions to run the build script:

chmod +x build.sh

Software

• Build agent requires:
  • Windows OS
  • PowerShell (current implementation)
• DevOps extensions:
  • SSIS DevOps Tools

Permissions

Each target SSIS server requires:

1. Add build agent account as SQL login
2. Create user in SSISDB for build agent login
3. Add the build agent user to the following roles in SSISDB:
   1. ssis_admin
   2. db_datareader
4. Create user in msdb for build agent login
5. Add the build agent user to the following roles in msdb:
   1. db_datareader
   2. SqlAgentUserRole
6. Create proxy user account on the target database
7. Add a proxy user account as SQL login
8. Grant the proxy user the required access permissions on the target database

Build

pip install --requirement requirements/build.txt
rm -rf dist/
./build.sh

This will create dist/sql-deployment-tools.exe.

Deployment Steps

1. (optional?) Create SSIS Folder
2. Deploy *.ispac artifact to SSISDB (using SSIS DevOps Tools extension)
   1. This can include the creation of the SSIS folder/project
3. Create SSIS environment
4. Create SSIS environment reference (links the folder with the SSIS environment)
5. Remove/reset all SSIS environment variables
6. For each project parameter:
   1. Create/set variable
   2. Mark variable as sensitive (optional)
   3. Set reference to SSIS folder/project
7. Create SQL Agent job
8. Create job step to execute package
9. Create job schedule(s)
10. TODO: Create agent operator (optional)
11. TODO: Create notification (optional)

Example Configuration for the SSIS Solution

To be placed in the SSIS package folder and included in the CI build. Optionally, the sql-deployment-tools executable can be downloaded and used to validate this config file in the pipeline.

project = "My Integration Services Project"
folder = "def"
environment = "default"

[[parameters]]
name = "name1"
value = "{SECRET_VALUE}"
sensitive = true

[[parameters]]
name = "name2"
value = "value2"
sensitive = false

[job]
name = "whatever"
description = "cool"
enabled = true
notification_email_address = "{NotificationEmailAddress}"

[[job.steps]]
name = "todo"
type = "SSIS"
ssis_package = "MyIntegrationServicesProjectLoad.dtsx"
proxy = "SSISProxy"

[[job.steps]]
name = "2"
type = "SSIS"
ssis_package = "MyIntegrationServicesProjectTransform.dtsx"
proxy = "SSISProxy"

[[job.steps]]
name = "3"
type = "T-SQL"
tsql_command = "SELECT TOP 10 * FROM sys.objects"

[[job.schedules]]
name = "name1"
every_n_minutes = 12

[[job.schedules]]
name = "name2"
every_n_minutes = 111

Note, when configuring a T-SQL step in an agent job, the default database will be master and so you should use three part naming in your script like so:

SELECT * FROM [Database].[Schema].[Table];

Note the {SECRET_VALUE} token in the above config.

This placeholder approach allows for source control of the configuration without storing secrets.

Secrets/tokens can then be injected at deployment time using the --replacement-tokens argument, e.g.:

sql-deployment-tools deploy --replacement-tokens '{"SECRET_VALUE": "***"}'

Example Connection String

Driver={SQL Server Native Client 11.0};Server=.;Database=SSISDB;Trusted_Connection=yes;