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.

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.

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.

3 thoughts on “Can I Automate SSIS Project Deployment?”

  1. 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!

  2. The wizard used in silent mode (for continuous integration/deployment) seems to have a bug where it’s not really silent? So with the silent switch, the wizard does not show any GUI, if the deployment is successful; however, if something goes wrong, the tool gives an error saying that it failed to show modal dialog, not the actual error.

    I’m wondering if this is a known issue and if Microsoft will be addressing it. For now, I’m considering TSQL API. Are there any gotchas there?

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">