Configuration - SQL Server Integration Services Design Patterns - page 411

Parameters can be set on connection managers using property expressions. The
most common property to set via expression is the ConnectionString , because
many connection managers derive their properties by parsing the Connec-
tionString value. When configuring connection managers, be sure to set expres-
sions on either the ConnectionString or individual properties—the order in
which expressions are resolved cannot be guaranteed, and certain properties may be
overwritten when the ConnectionString is applied.
To parameterize a shared connection manager, open one of the packages in the pro-
ject and right-click on the shared connection manager's name in the Connection Man-
agers area of the design surface. Note that since shared connection managers are de-
clared at the project level, you can only use project-level parameters or static strings in
any property expressions on shared connection managers. The expression dialog will
not give you the option to use package parameters or variables.
Parameter Configuration on the Server
Parameters were designed to make it easier for the person scheduling and running SSIS
packages. In many environments, this is typically a DBA or IT operations person—not
the person who originally developed the package. By including descriptions with the
parameters, an ETL developer can create self-documenting packages, making it very
easy for whoever is configuring the package to see exactly what it needs to run.
This section describes how to configure packages through the SSIS Catalog and
how to surface parameters through SSMS. It covers how to set default parameter val-
ues after a project is deployed, the various package execution options, and how the
built-in reporting functionality in SQL Server 2012 makes it easier to determine the ex-
act configuration values set when the package was run.
Default Configuration
Default values for all parameters and connection managers are saved within the SSIS
project deployment file ( .ispac ) when the file is built. These become the default val-
ues for the project once it is deployed to the SSIS Catalog. To change the default con-
figuration, right-click on the project name (or individual package names) and select
Configure within the SSMS Object Explorer (as shown in Figure 17-10 ).