So you’ve developed an amazing SSIS package. It runs flawlessly on your local machine and every little task brights up with this joyful green. But what now? You can’t keep running this package in Business Intelligence Development Studio (BIDS). If the package needs to run during the nightly processing window, they can’t expect you to sit by your computer to execute the package at 2 A.M., can they?

You need to deploy your package to a server. Preferably a development or test server, because you need to test if your package integrates well with the entire environment. And because if your DBA has done his work, you’re not even allowed to look at the production server.

This introductory article describes various methods on how you can deploy your individual packages stored on your local desktop into the server. The project deployment model, introduced in SQL Server 2012, is outside the scope of this article. However, the package deployment model of SSIS 2012 can use the same methods described here.

The Destinations

There are several possible destinations for your package:

• The file system. The packages are stored in a folder you choose.
• The SSIS Package Store. The packages are saved to a set of file system tables managed by the SSIS Service,  located in %Program Files%\Microsoft SQL Server\100\DTS for SQL Server 2008.
• The MSDB database. The packages are stored in the dbo.sysssispackages table for SQL Server 2008.

When you log into the SSIS service using Management Studio, the node Stored Packages displays all the packages stored in the SSIS Package Store – labeled File System – and the MSDB database.

Each deployment method allows you to save the package into one of these three destinations. From now on, I’ll assume we’re deploying to the MSDB database. This is my favorite destination, because if you have a backup plan for the MSDB database, your SSIS packages are also included in this backup.

The four ways of deploying a package are:

• The traditionalist way
• The manual way
• The old school way
• The pragmatic way

We'll describe each of these below.

The Traditionalist Way

Here we use the built-in deployment method to get our packages to the server: the SSISDeploymentManifest. This is basically a simple XML describing which packages need to be deployed to the server. If you double click this manifest, a wizard will launch that assists you in deploying the packages to the server.

Follow these steps to create the manifest:

1. Right-click on the project and select Properties. In the Configuration Properties, select the Deployment Utility pane. Set the CreateDeploymentUtility property to True. You can see this in Figure 1 below.

2. Right-click the project and select Build. This will create the manifest in the folder you configured and copies all the packages to that folder.

3. Go to the deployment folder, default .\bin\Deployment, and double click on the manifest to launch the Package Installation Wizard.

4. Follow the wizard to deploy your packages. More information can be found here.

Figure 1

The Manual Way

For people who like to have control (DBA anyone?) and do everything by hand: you can deploy the packages manually.

1. Log into the SSIS service using Management Studio and browse to the folder where you want the package deployed to.
2. Right-click on the folder and choose "Import Package"
3. In the dialog, specify the source of the package, locate the package on your desktop and click OK.

An alternative for people who like to stay in BIDS:

1. Open your package. In the menu, go to File and select "Save Copy of [myPackage.dtsx] As" This is shown in Figure 2 below.
2. You’ll receive a dialog. Choose the destination location and select the path where the packages need to be deployed. Hit OK.

Figure 2

The Old School Way

You’re on of these guys who absolutely adore command lines. You can directly use the command line utility dtutil to deploy your package. A typical command line to deploy a package to MSDB would look like this:

dtutil /FILE “C:\myPackages\myPackage.dtsx” /DestServer myServer /Copy SQL;myPackage

Note that  that the destination package name doesn’t have the .dtsx extension.

2.     The pragmatic way

The last and my favorite way of deploying packages to a server is using BIDS Helper, a free add-in for BIDS. This neat little add-in adds a ton of features to BIDS and the one of most interest to us is the simplified deployment feature.

1. First you need to configure the deployment properties. Right-click on the project and select Properties. In the Configuration Properties, select the Deploy (BIDS Helper) pane. Choose your favorite destination type and configure the location.

2. Now you can simply deploy a package by right-clicking on it in the Solution Explorer and by selecting Deploy. The result of this deployment can be consulted in the Output window of BIDS.

   The biggest advantage however is that you can easily deploy multiple packages at once: simply right-click on your project and select Deploy. All the packages in the project will now be deployed!

   Conclusion

   This article explained different methods on how you can deploy your packages to a server. Each of them gives the same end result. So choose the one you’re most comfortable with and deploy your package!