Can I Automate SSIS Project Deployment?

Yes, yes you can. Scripted or automated deployment can be done in a number of ways in SQL Server 2012.

Integration Services Deployment Wizard

Yes, this is the primary UI tool for SSIS project deployment, but it can also be run silently on the command line. When you run through the wizard, the Review page will actually list all of the parameters you need to do the same deployment from the command line.

image

Run ISDeploymentWizard.exe /? from a command prompt, and you’ll see the full list of arguments it supports.

image

SSIS Catalog T-SQL API

The SSISDB [catalog] schema has a number of public stored procedures, including one that can be used for deployment. We even provide samples on how to use it in Books Online.

DECLARE @ProjectBinary as varbinary(max)
DECLARE @operation_id as bigint
Set @ProjectBinary = (SELECT * FROM OPENROWSET(BULK 'MyProj.ispac', SINGLE_BLOB) as BinaryData)

Exec catalog.deploy_project @folder_name = 'MyFolder', @project_name = 'MyProj', @Project_Stream = @ProjectBinary, @operation_id = @operation_id out

PowerShell

All SSIS Catalog operations can be automated through PowerShell. I previously blogged about a deployment script I use to setup my demos.

Custom Utility

The SSIS Catalog management object model (MOM) exposes a set of SMO classes you can use to code your own catalog utilities. You’ll want to use the CatalogFolder.DeployProject method to do the actual deployment. If SMO or .NET isn’t your thing, you can also code a custom utility which interacts directly with the T-SQL API.

2 Responses to “Can I Automate SSIS Project Deployment?”

  1. ToddB says:

    I really appreciate you putting this information up. I'm an application developer looking to automate SSIS deployment through team build/deployer.

    My assumption is that powershell/MOM is the way to go, but can you tell me if you agree? Does the wizard support everything needed, including creating the folder, environment and populating variables? I'm guessing not.

    Thanks!

Leave a Reply

You must be logged in to post a comment.