Project & Package Deployment Methods – SSIS Basics

As a newbie to SSMS and SSIS, one of the hardest things to wrap my head around was the difference in the way in which projects and packages are deployed.

After diving into Books Online, as well as playing around with SSIS endlessly, I came up with the chart below, which illustrates some of the differences between the project and package deployment models.

projectvspackage

Build

When a project is built, all of the files in the project are stored in an .ispac file, which is basically an archive containing all of the package files (.dtsx), project-level parameter and connection files, and a few others.

When a package is built, the package files are stored separately as .dtsx files, and a Deployment Utility file (.manifest) is also generated if the CreateDeploymentUtility option is set to True.

UI-based deployment method

Projects are deployed with the Integration Services Deployment Wizard, which can be initiated by either right-clicking the project in SSDT and selecting Deploy, or by double-clicking the .ispac file stored from the build. The first method will generate a new .ispac file to work with anyway.

Packages are deployed with the Package Installation Wizard. This can be accessed by double-clicking on the .manifest file generated from a build. The packages can then be deployed to either a file system location, or SQL Server, in the SSIS Package Store.

(Automated) Command line deployment method

For projects, use the following command line:

ISDeploymentWizard /S /SP:"MyProject.ispac" /DS:"MYSERVER" /DP:"/SSISDB/MyFolder/MyProject"

When the /S[ilent] option is not used, the UI version of the wizard will appear. It’s a good idea to first use the UI version to generate a command line to work with, which is displayed on the last page of the wizard.

The options for this method are as follows:

ISDeploymentWizardOptions

For packages, use the following command line.

dtutil /File DimCustomers.dtsx /COPY DTS;MSDB\DimCustomers

The above line deploys a package from the file system, to MSDB in the SSIS package store. The following code is an alternative that achieves the same thing:

dtutil /File DimCustomers.dtsx /COPY SQL;DimCustomers

A lot of abbreviation can be made with the options, so it pays to memorize what they all mean:

dtutilOptions

For more examples of how to use dtutil, be sure to take a look at books online.

Try using the methods mentioned above, and let me know how you go!

 

Advertisements