Chapter 10: Securing and Deploying SSIS Packages

 Download CD Content

Overview

After completing this chapter, you will be able to:

• Understand the components of package security and learn to automate package deployment to a server.

• Understand package deployment options: the package deployment utility, command-line options, and package importing and exporting.

• Use role-based security to protect deployed packages for execution and administration.

• Learn to monitor package execution and configure and view SSIS event logs.

Up to this point, you've designed packages in Business Intelligence Development Studio (BIDS) and transformed, imported, and exported small amounts of test data in the development environment. The package designer and development platform does a good job of emulating true package execution, but it isn't the same for at least two reasons. In the designer, a package runs with debugging checks and additional overhead that use up memory and CPU cycles, which can throttle back performance and capabilities. In a deployed server environment, Microsoft SQL Server Integration Services (SSIS) will dedicate more resources to package execution, buffer space, memory allocation, and parallel processing. To get the best performance out of SSIS and to work with large volumes of real business data, you must deploy and execute packages on an enterprise-class production server. In this chapter, you will learn how to make the transition to a deployed server environment.

Security and deployment are managed at the package level. Optionally, you can deploy all packages for a project by building a deployment manifest and using the deployment utility and Package Installation Wizard. A package can be secured by password to protect it from being edited by unauthorized users. Further, information stored within the package can be encrypted. This can be applied to only passwords and other sensitive information or to the entire package.

After a package has been deployed to the server, it can be executed manually or scheduled to run automatically. While a package is executing, you can view its status from SQL Server Management Studio (SSMS). After package execution has completed, you can also use SSMS to view the SSIS logs to obtain detailed information about errors and execution status.