SSIS: Deploy Packages, Part 1

This is the companion text to the video SSIS: Deploy Packages.  This will be a two- or three-part series, I think.

There are two ways to deploy SIS packages, and the difference becomes important when you have a lot of packages. The first way is just to individually deploy a package directly to SQL (or the file system). The second way is to create a manifest, which allows you to deploy all the packages at once, instead of having to do one at a time. Today we’re talking about the manifest.

A quick side note: When you create a new Integration Services (SSIS) package in Visual Studio, it's named Package1.dtsx by default.  Always rename your package to something meaningful, and select Yes in answer to "Do you want to rename the package object as well?" See the blog titled SSIS Packages: Rename the Package Object as Well? for more information.

Deploy with Create Deployment Utility

When you have a package ready in Visual Studio,

  1. Go to the Project menu and select Properties.
  2. Under “Deployment Utility”, Set "Create Deployment Utility" to True, which creates a manifest file when you build the project.  (By the way, does anyone know how to set this by default?)
  3. “OK” out of the Property dialog.
  4. Save your project, and Build. 
  5. Go look in your project folder. In our example, the project folder was in My Documents\Visual Studio 2008\Projects\Integration Services Project2\Integration Services Project2\bin\Deployment\.

In there you have a your package files named packageName.dtsx file, and one deployment manifest – an XML file named projectName.SSISDeploymentManifest.  You can open the manifest in Internet Explorer to take a look.  It tells you what it's generated by, who did it, and a list of packages. You could create this by hand easy.  As a matter of fact, Sean wrote a .Net desktop app to create a manifest file from a list of packages. “From the developers I was getting nested folders with individual files; I had it make a master manifest file.”  Sweet, sweet automation…

Now, we could go to SIS and import the .dtsx file just fine, but we'd have to do that individually for each package. When we double-click the manifest, it brings up the package installation wizard which covers all the packages.

You can deploy packages to SQL or let them sit on the file system. That's something new with SIS: In DTS your packages were in SQL; in SIS you can run them t from the file system.

Package Location

So SQL Server deployment deploys the package and stores it in MSDB.  I could instead point it to a file system; it would still import the metadata into MSDB but the package itself would live on the file system.  The advantage to this is that you have a single package. It's easier to have one package & edit it as needs be, and several different marts can call it from the same place on disk.

Back in DTS you could go into Enterprise Manager and double click on a package and edit.  However, after a package is deployed from SIS, it's compiled code.  Consider these to be like any C# application: they're exe files.  So you have two choices for editing SIS packages:

  1. go into SSIS manager, right click and export package to file system, and then edit in VS and redeploy.
  2. OR you can store the package in VSS or TFS server, some code vault, and edit/deploy that way.

-Jen McCown, http://www.MidnightDBA.com