Datasheet

Chapter 1: Welcome to SQL Server Integration Services
10
Connection Managers are used to centralize connection strings to data sources and abstract them from
the SSIS packages themselves. In fact, the connections created by a Connection Manager are typically
created in the registry of a machine and not stored in the package itself — although you can encrypt this
information and store it. This allows you to deploy the SSIS package with a configuration file (which
we ’ ll describe later) that can set the full value of the connection properties at runtime. One nice thing is
that you can even configure the connection offline and completely design an SSIS package without
connecting to the server until you are ready to test. SSIS will not use the connection until you begin to
instantiate it in the package. This provides the ultimate in lightweight development portability for SSIS.
Data Source Views
Data source views (DSVs) are handy abstract concepts that you can use in SSIS and other SQL Server
projects. This feature allows you to create logical views of your business data. These views are a
combination of tables, views, stored procedures, and queries that can be shared across your project and
leveraged in Analysis Services and Report Builder projects.
This is especially useful in large complex data models that are prevalent in ERP systems like Siebel or
SAP. These systems have column names like ER328F2 to make the data model flexible for nearly any
environment. However, this complex naming convention creates difficulties for the typical business user
or developer, impeding productivity that a simple readable name would eliminate. A DSV can be used to
map such columns to entities like LastPaymentDate to increase readability of the model. DSVs can also
be used to map the relationships between the tables that don ’ t necessarily exist in the physical model.
Another common use of DSVs is to segment large sections of the data model into more security - or
functional - based chunks. DSVs provide an abstraction that transcends schema or even data source
separation. Take, for example, a DSV from the AdventureWorks Human Resource model as shown in
Figure 1 - 5. As you can see in this figure, not only has the DSV unified the different schemas, but a
friendly name has also been assigned to rename the Birth Date column in the Employee entity to DOB.
Figure 1-5
c01.indd 10c01.indd 10 8/28/08 12:01:18 PM8/28/08 12:01:18 PM