CHAPTER 3 Deploying and Configuring SSIS Packages

Chapter Summary

1. Through SSIS package configurations, you can store package properties outside the package. The available properties can come from various source objects: package-level objects, control flow objects, variables, connections, and so on.
2. Configurations can be stored in the registry, in an XML file, in an environment variable, in a parent package variable, or in a SQL Server table.
3. Packages can use more than one configuration. Multiple configurations are applied in order, and packages can share configuration entries.
4. SSIS includes an expression language for dynamically configuring some control flow and data flow objects. One valuable application of the expression language is property expressions, which allow properties in a package to be updated at run time.
5. You can deploy packages to SQL Server or the file system either manually or by using the Package Deployment Utility in BIDS.
6. The SSIS built-in deployment is a two-step process that consists of creating a deployment installation kit through the Package Deployment Utility and then deploying the installation kit by using the Package Installation Wizard.
7. SSIS comes with the DTUtil command-line utility, which can copy, move, delete, encrypt, and digitally sign packages.

Lesson 1: Using Package Configurations and Expressions

1 . What are some reasons to use package configurations in your SSIS architecture?

SSIS package configurations are valuable when you have an SSIS environment in which you need to deploy packages from one server to another and the properties of those packages, such as their connections, need to be updated.

2 . When does a package read and use the configuration entries?

Configuration entries are read at the start of the package execution. In BIDS, configurations are applied only when you execute a package in the debug mode; they are not applied when you are designing your package.

3 . When are property expressions evaluated as a package is running?

unlike configuration entries that are read at the start of the package execution, property expressions are updated when the property is accessed by the package during package execution. A property expression can change the value of a property in the middle of the package execution, and the new value is read when the property is needed by the package.

Lesson 2: Deploying SSIS Packages

1 . What items are created when the CreateDeploymentutility property for the SSIS project is set to True and the project is built? Where can you find these items?

A copy of all the packages, XML configuration files, the project's miscellaneous files, and the installer kit configuration file are all created with the deployment utility. These files are copied to the location specified in the project's DeploymentoutputPath property. The default value is [project path]\bin\Deployment, where [project path] is the location of the project you are working on.

2 . What are the two types of destinations to which an installer kit can deploy SSIS packages?

SSIS packages can be deployed to either a file system or a SQL Server database, and the installer kit will prompt you to choose one of these.

3 . Can you use DTutil to delete a package that is deployed to SQL Server?

Yes, you can use DTutil to delete packages in the file system and in SQL Server by using the /DELETE command-line switch.

Deploying SSIS Packages

Case scenario

You are asked to manage a set of 25 SSIS packages generated by a development team. These packages perform data consolidation from three source systems: IBM DB2, Oracle, and flat files exported from a legacy system. The destination is a SQL Server database that is used for Customer Relationship Management (CRM) and call-center reporting. The sources do not support Windows Authentication, but a user name and password are provided immediately after the monthly password change as part of the company's security-compliance policy. You need to define deployment procedures and make the packages independent of source userconnection information. How would you satisfy the following requirements?

1. The packages must point to the correct servers and be updated with the user names and passwords—all without editing the packages in production. However, the packages the development team gave you contain hard-coded connections. How can you implement a flexible architecture to handle the changes?
2. Your organization has invested in a test environment that mirrors the production environment, so the deployment objective is to deploy all the packages for a development cycle to the test server, on which the packages can go through an extensive quality assurance process before they are deployed to production. Your deployment strategy needs to include a complete set of files with an automated deployment process, and your packages need to be deployed to SQL Server for both environments so that the packages can be backed up through your database backup procedures. How do you proceed?
3. Occasionally, as bugs are identified, you are given a single SSIS package to deploy. To minimize human error in the deployment, you need an automated procedure to deploy the file to the test and development servers. How can you achieve this?

1. To make the connection strings configurable without manual package editing every time a server or user name and password changes, you should implement package configurations. Across the 25 packages, only two source systems contain user names and passwords (the files are on a network share), so you should create a single XML file that contains the connection strings. First you would create the configurations file in one package by using SSIS package configurations, and then you would have all the packages point to the same file. If your policies prevent storing user names and passwords in a flat file, you can choose to use a Microsoft SQL Server table to store these items. If you do use a SQL Server table, be aware that you will have a second configuration entry that will be applied first—it will point to the SQL Server database in which the connection strings are entered. You will need to replicate the SSIS package configurations on the test and production computers, being sure to use the appropriate server, user name, and password entries where applicable. This way, as changes happen, you can modify the connections without opening the packages.

2. Because SQL Server is the package destination for both the test and production environments, an easy way to automate your deployment is to create a deployment installer set by using the Package Deployment Utility in BIDS. You can run the Package Installation Wizard on your test server and deploy all the packages to SQL Server in one step. When the tests are complete, the same installer set can be deployed on your production server through the Package Installation Wizard.

3. You can deploy single SSIS packages manually, but a better choice is to use the DTUtil command- line tool, which lets you write a command for the deployment and then automate the process. You could deploy the package to the local SQL Server using Windows Authentication by including a simple command, such as this:

dtutil.exe / FILE c:\IntegrateCRM.dtsx / COPY SQL;IntegrateCRM