Deploying SSIS Packages
Now that all of your packages have been created, you will need to deploy them to either an
instance of SQL Server or a file system location using one of the many approaches that are avail-
able. Each approach ultimately accomplishes the same goal; however, the method of choice will
probably depend on the number of packages you plan to deploy. As with most Microsoft tools,
you have numerous ways to accomplish the same task. Both Business Intelligence Development
Studio (BIDS) and SQL Server Management Studio (SSMS) include tools and wizards that sim-
plify the deployment of SSIS packages. There are three main deployment choices available to you:
Deploy a single package using SSMS.
Deploy a single package to a SQL Server instance or file location using BIDS.
Create a deployment utility using BIDS.
The first two methods allow you to deploy only a single package at a time. The third approach,
on the other hand, allows you to configure and deploy multiple packages simultaneously.
DEPLOYING A SINGLE PACKAGE USING SSMS
If you are deploying only a single SSIS package this maybe a viable approach. Using SSMS, con-
nect to the SSIS instance to which you will be deploying the package. As stated earlier, you can
deploy a package to an instance of SQL Server or to a file location. When you are connected to
an SSIS instance, expand the Stored Packages folder in the Object Explorer (Figure 10-1).
Right-click the folder labeled MSDB and the Import Package screen will appear (Figure 10-2).
On this screen you will select the package location, which in this case will be File System.
Now you must enter the SQL Server name and the username and password if you are using
SQL Authentication. If you are going to connect with your Windows account, choose Windows
Authentication. This authentication method will use your Windows domain credentials to
deploy the package. Next you will use the ellipsis button next to the “Package path” text box to
locate the package that you plan to upload. Then you are required to provide a package name
10
889633c10.indd 87 8/16/10 9:13:26 PM
88
LESSON 10 Deploying SSiS packageS
(the field is pre-populated with the current package name). You can accept the default or you can
change the name of the package. Finally, you must specify your package protection level. Once you
are finished, click OK and your package will be deployed.
FIGURE 101
This approach is limited in two ways. First, you can update only a single package at a time. If you
have several packages to deploy, this could be a very cumbersome approach. Second, if your pack-
ages rely on any configuration files, you will be required to change the values manually either before
or after deployment. As a result, this method may not be optimal for a multi-package deployment
that includes configuration files.
DEPLOYING A SINGLE PACKAGE USING BIDS
You can also deploy a single SSIS package using BIDS. First, open the SSIS project that contains the
package you want to deploy. Once the project is open, you will need to open the package that will be
deployed. Then, select FileSave Copy of Package As, and the Import Package screen (Figure 10-2)
will again appear. You will follow the same steps outlined in the previous section to deploy your
package. As when deploying a package using SSMS, you are limited to deploying one package at a
time and cannot modify your package configurations at the time of deployment. Keep this in mind
when deciding on your package deployment strategy.
CREATING A DEPLOYMENT UTILITY USING BIDS
If you are planning to deploy more than one package and require a more flexible SSIS deployment strat-
egy, I suggest that you use this approach. There are three steps required to build the deployment utility,
and one optional step:
1. Create a package configuration file (this is the optional step).
2. Create an SSIS project, set the CreateDeploymentUtility property of the project to true, and
build the project.
FIGURE 102
889633c10.indd 88 8/16/10 9:13:26 PM

Get Knight's Microsoft® Business Intelligence 24-Hour Trainer: Leveraging Microsoft SQL Server® Integration, Analysis, and Reporting Services with Excel® and SharePoint® now with the O’Reilly learning platform.

O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.