Scripts for repeatable SSIS package deployment

Instead of writing manuals with a lot of pictures for a customer's admin, I prefer to write a script that does the deployment by itself when ran. Several times I'd needed to deploy SSIS packages and configure them with SQL scripts, which is a pain to do manually, since it involves a lot of clicking and SQL editing. Of course, another advantage of using scripts is that your deployments are repeatable, quick, and can be shared with your colleagues via your VCS of choice. All of the components used here should be present on any SQL server you are deploying to - no additional installation should be necessary.

If you just want the scripts, they' re in this gist. Here are some basic usage instructions, but please use the gist link for the most up-to-date version of the code.

There are 4 files total. deploy.ps1 is the main file that orchestrates the deployment, and the only one that you need to run. database.sql contains SQL configuration, and optionally, your SQL initialization scripts. deployConfig.ps1 has all the connection strings and other values that will be used to configure this deployment. (If you have one deployConfig for each environment, you needn't change anything else.)

. .\ssisDeployment.ps1 
. .\deployConfig.ps1
write-output "Initializing SQL DB"
. sqlcmd -S $config.integrationServerName -i .\database.sql
write-output "SQL DB initialized"
write-output "`nDeploying SSIS package"
deploy-ssis @config
write-output "Done"

• The first two lines just dot-source the needed deployment command (deploy-ssis) and the configuration dictionary, which in this case will be in the $config variable.
• I then use sqlcmd to configure the SQL server. If you need to configure the packages you're deploying, you will have to call sqlcmd again, after deploy-ssis (since the package doesn't exist at the server yet.)
• The only thing left to do is the actual deployment - just pass the configuration values into deploy-ssis. You can pass them in as individual parameters, but I prefer to keep then in a single, version-controlled dictionary and splat. Note that deploy-ssis will deliberately block until the deployment is completed, so the rest of your scripts can count on the packages being present in the server.

The deploy-ssis itself does all the initialization needed - including creating the SSIS catalog and project folder.

Example of the deployConfig file:

$config = @{
  'ssisProjectName' = 'My SSIS Project';
  'integrationSqlServerConnectionString' = 'Data Source="server.path.here";Initial Catalog=Integration;Integrated Security=SSPI;';
  'ispacPackagePath' = '.\MyPackage.ispac';
  'ssisCatalogPassword' = 'SuperstrongPassword31337';
  'ssisFolderName' = 'My SSIS Folder';
  'integrationServerName' = 'server.path.here';
};

Probably the only parameter of deploy-ssis that isn't self-explanatory is sqlServerVersion, which defaults to 120. It's the version of your SQL server, as present in the path to ISDeploymentWizard. So for SQL server version 12.0, it will be 120, and the path will be ProgramFiles\Microsoft SQL Server\120\DTS\Binn\ISDeploymentWizard.exe.

To sum up, in order to use these scripts:

1. Save them in a single folder
2. Edit the deployConfig.ps1 to include your values
3. Run .\deploy.ps1

Get them from this gist.

(Source image taken from here)