Deploying Packages

So, you need to move the package? Integration Services provides a utility for moving packages, but for a moment, let's take a step back and think about the deployment problem. What is it you're trying to accomplish? Is there something in the package, some setting or value that can't be moved by simply copying the package to another machine? Not really. However, problems arise when you move a package that references external resources that are available on one machine that aren't available on another. For example, no amount of configuration magic is going to help if you attempt to run a package that references a custom task that isn't installed on the destination machine.

So, the difficult challenge with deployment isn't about moving the package per se. It's really about getting the destination machine to "look" like the machine where the package was built. If the package references a custom task, user account, configuration file, or share on the development machine, the custom task, user account, configuration file, and share must be available on the destination machine. After the destination machine "looks" like the source machine, deployment becomes a simple matter of copying the package. Integration Services provides a way to address some of these issues, notably moving packages and the files they depend upon, in the form of a wizard. This section explains how to use the Deployment Wizard and when it is appropriate and when it is not appropriate to do so.

The Deployment Utility

The designer provides a way to bundle the project packages and miscellaneous files into a folder that you can copy to another machine. The Deployment Utility is a small executable that knows how to install the bundle. When you install the bundle by executing the Deployment Utility the Deployment Utility provides a simple wizard called the Package Installation Wizard to walk you through installing a package. To enable deployment in the designer, right-click on the Integration Services project node in the Solution Explorer and select Properties. Figure 14.13 shows the property pages for the project.

Figure 14.13. Use the Property Pages dialog box to set up deployment

There are three properties that control deployment behavior:

• CreateDeploymentUtilityThis is probably a misnomer because it doesn't actually create the Deployment Utility. Rather, it creates the bundle of files that the Deployment Utility installs. Essentially, this tells the designer to copy all the packages and miscellaneous files to a folder and build a deployment manifest, which are described shortly.

• AllowConfigurationChangesThis setting is stored in the deployment manifest and instructs the Deployment Utility to allow the user to make configuration changes.

• DeploymentOutputPathThis setting instructs the designer where to place the deployment manifest, packages, and miscellaneous files.

After enabling deployment, build the SSIS project by right-clicking on the project in the Solution Explorer and selecting Build. The designer copies all the packages, miscellaneous files, and XML configuration files the packages reference to the folder specified in the DeploymentOutputPath setting. Every time you build, it does this. It also creates a file with the SSISDeploymentManifest file extension. The manifest file looks similar to the following:

[View full width]
<?xml version="1.0"?> <DTSDeploymentManifest GeneratedBy="ALPHA\Kirk" GeneratedFromProjectName=  "S14-Configurations" GeneratedDate="2006-12-31T00:39:17. 6393750-08:00"  AllowConfigurationChanges="true">   <Package>     ParentPackage.dtsx   </Package>   <Package>     ChildPackage.dtsx   </Package>   <Package>     Configurations.dtsx   </Package> </DTSDeploymentManifest> 

The DTSDeploymentManifest contains some metadata about how and when the manifest was created. The AllowConfigurationChanges specifies that the wizard should show the Configure Packages dialog box after copying all the files. And, of course, the Package nodes specify which packages to copy.

To launch the Deployment Utility, double-click on the manifest file in Windows Explorer. The Package Installation Wizard appears, as shown in Figure 14.14.

Figure 14.14. The Package Installation Wizard

File System Deployment

As described, if you select the File System Deployment option, the wizard moves all the dependent files, including XML configurations and the files in the miscellaneous node of the project, to a location on the disk. Figure 14.15 shows the next dialog box, Select Installation Folder, in the wizard.

Figure 14.15. The default installation folder

The Deployment Wizard selects the Server Store folder by default. This is the folder where the Integration Services service looks to enumerate packages in the file system. If you look in SQL Server Management Studio under the Stored Packages, File System node, you'll see all the files stored in that folder. Figure 14.16 shows the packages after being deployed.

Figure 14.16. The packages stored in the File System folder shown in SQL Server Management Studio

The Magic Folder

On the IS team, this folder is fondly referred to as the "Magic Folder." You can change the location of this folder in the MsDtsSrvr.ini.xml file if you want. Chapter 17, "SQL Server Management Studio" covers this in more detail.

SQL Server Deployment

If you select SQL Server Deployment, the wizard moves the packages to the SQL Server you specify in the dialog box shown in Figure 14.17.

Figure 14.17. The packages stored in the file system shown in SQL Server Management Studio

Because SQL Server package storage only stores packages, the other dependent files must go somewhere, so the wizard places them in the magic folder by default. The next dialog box in the wizard (after the one shown in Figure 14.17) allows you to change the folder where the dependent files are stored.

Because the option to edit the configuration was specified in the Project Properties dialog box, the wizard now provides a way to modify the configuration. After the wizard moves all the packages and dependent files, it shows the Configure Packages dialog box, as shown in Figure 14.18.

Figure 14.18. Use the wizard to update package configurations during deployment

If the package references more than one XML configuration, the Configure Packages dialog box allows you to select which configuration to edit using the Configuration File drop down, as shown in Figure 14.18. You can use this dialog box to modify the configuration to have the correct value for a given configuration. For example, in Figure 14.18, no values are specified for SaveCheckpoints and BufferTempStoragePath, and so on. Using this dialog box, the values can be updated to reflect the destination machine.

Deployment Wizard Usage

Let's take another step back now and review what the Deployment Wizard does.

• Copies packages It copies packages to SQL Server or the file system.

• Copies XML configurations It copies XML configuration files the packages reference.

• Copies dependent files It copies the files in the Miscellaneous Files node of the package project.

• Modify configuration It allows you to modify the configurations.

The Deployment Wizard does not do the following:

• Move custom components It doesn't move custom components to the destination machine, such as custom tasks or Data Flow transforms, upon which packages rely.

• Recognize deltas It doesn't recognize when significant changes happen in the package or dependent files or if they are unchanged. Nor does it modify what it deploys based on such information. It simply copies all the files every time you run it.

• Detect external dependencies It doesn't recognize when a package depends on a file that is not included in the Miscellaneous Files node.

So, by now, you might be wondering what the point is. And here it is: Over and over, people misuse the Deployment Utility. The Deployment Utility should only be used in certain circumstances, as follows:

• When you need to create an installation for a new package that has never been installed on a machine before

• When you want to overwrite or otherwise update the dependent files

After you've run the Deployment Wizard on a machine and installed the package, unless you need to update the changes affected by the Deployment Wizard, there is little else to do but copy the package. Is the Deployment Wizard useful for the purpose for which it was designed? Absolutely. Should you use it to repeatedly copy the same package to the same location while developing or updating a package? No. It doesn't gain you anything. In fact, you'll be duplicating efforts and possibly losing or corrupting configurations by overwriting existing configurations or by human error.

So here's a rule of thumb: Only use the Deployment Wizard when something other than the package requires updating. If only the package has changed, simply copy the package or save it to SQL Server.

Moving Packages Around

You can move packages in many ways that are more efficient than the Deployment Wizard, as follows:

• XCopy/Copy You can use the file system commands for moving packages to different file system locations. This is a tried-but-true method.

• DTUtil Use the DTUtil utility to move packages to SQL Server, the file system, or the SSIS server. DTUtil has many useful functions for manipulating packages. See Books Online for more information.

• Save Copy As The Save Copy of <packagename> As menu option in the main File menu of the Business Intelligence Development Studio provides a dialog box for saving packages directly to the file system, SQL Server, or SSIS server. Click on the package Control Flow design surface to ensure this menu option is visible.

• Import Package In SQL Server Management Studio, in the Object Explorer under the Integration Services nodes, you can right-click on the File System or MSDB nodes and select Import Package. This allows you to pull a package into those locations.

• Export Package After you've saved a package to SQL Server or SSIS Server, you can retrieve it again from those locations to a file system folder using the Export Package menu option on the context menu you get by right-clicking on the package nodes in Object Explorer.

Tip

My suggestion is to use the Deployment Wizard whenever you need to move a new package and configurations to a location for the first time and then use one of the preceding methods for moving the package from then on. The only time you might want to use the Deployment Wizard again for the same package and location is if the package or configuration files change significantly. Even then, I would hesitate because you might unintentionally overwrite some settings in a configuration.