In this installment of our series dedicated to SQL Server 2005 Integration
Services, we will look into functionality intended to improve efficiency of
managing Business Intelligence Development Studio-based projects. In
particular, we will focus on features that help maintain consistency across
multiple packages and increase their portability (facilitating their deployment
into production environment or distribution of multiple replicas of the same
code onto different systems). The topics covered here will include package
templates, data sources, and data source views, which supplement some of the
earlier discussed, similar mechanisms, such as configurations
or SSIS
Deployment Utility.
If you create SSIS packages on a regular basis, it might turn out that, for
each of them, you keep repeating an identical sequence of steps in order to
modify their default settings so they match your preferences. Such an approach
introduces unnecessary overhead and leaves room for mistakes and omissions. A much
more convenient and less error-prone alternative involves using custom package
templates, which are simply regular packages, pre-configured in a desired
manner and stored in a designated location. This approach gives you an option
to delete the default package, automatically included in your Integration
Services projects initiated with Business Intelligence Development Studio, and
add the one you have defined. There is, however, a caveat that you need to be
aware of if you decide to proceed with the second option. Among settings
inherited from your custom template are package Name and ID (in the form of
16-byte GUID) properties. It is recommended that you change both in order to
avoid potential confusion (especially if you intend to track package execution
with SSIS log providers, since they will contain identical entries generated by
different packages).
To implement templates, start by creating a new project of Integration
Services type in the Business Intelligence Development Studio. Modify its default
package by adding to it any components you are planning on reusing, including
both Control Flow and Data Flow items, connection managers, event handlers, log
providers, configurations, variables, and security settings (for example, you
can ensure consistent protection level across all of your environment and avoid
issues associated with the default EncryptSensitiveWithUserKey option that we
described in our
earlier article on SSIS Security). After you have applied all
customizations, ensure that the package entry, which appears under the SSIS
Packages folder in the Solution Explorer window is highlighted and choose Save Package
As… (where Package represents the package name) from the File heading
in the main menu. Point to %Program
Files%Microsoft Visual
Studio
8Common7IDEPrivateAssembliesProjectItemsDataTransformationProjectDataTransformationItems
folder as the target location for the template and assign an arbitrary name to
it. Once this step is completed, you can incorporate the package into another
project by using the Add -> New Item… entry in their context sensitive
menu within the Solution Explorer window (and remove the default one). This
way, its copy will be created in the folder hosting other project files, with
the name formed by concatenating the template file name and an integer
(starting with 1 and incremented for every new template-based package added in
the same manner).
To assign a new name to the package object, right-click anywhere within a
free (not obscured by any component) area of the Control Flow tab in the SSIS
Designer interface and select the Properties item from the context-sensitive
menu. Locate Name property in the Properties window and change its value
according to your preferences. Next, scroll to the ID entry and use the Generate
New ID option in the drop-down list of its value field to alter Package ID
(alternatively, you can accomplish the same goal by running the DTUtil command
line utility with /IDRegenerate switch).
In addition to employing templates and configurations, another method to
simplify maintenance of package settings (as well as to increase their
portability) is through implementation of data sources and data source views.
Data sources are similar in many aspects to package connection (since they
represent a connection to a specific data store, which takes the form of a
connection string and data store identifier, along with relevant authentication
information), although they are not limited to the context of an individual
package. Instead, their scope is solution-wide, allowing them to be shared by
multiple packages within the same project as well as copied across multiple
projects (both Integration Services and Analysis Services) within the same
solution. Data sources typically serve as a basis for defining connections
within SSIS packages (using the New Connection From Data Source… option in the
context sensitive menu of the Connection Managers area in the SSIS designer
window), which inherit their main characteristics, including connection
strings, data store identifier, and related metadata (such as authenticating
credentials). This inheritance is maintained through the connection’s DataSourceID
property, which value gets set to the name of the originating data source. This
way, any subsequent changes to data source are automatically applied to all
connections that were derived from it. Similar synchronization mechanisms can
be maintained between multiple copies of data sources within the same solution.
Such approach facilitates package portability, simplifying modifications
necessary when switching from development to production environments or when
distributing packages across a number of servers.
It is important to note that Data Sources are accessible only at design time
within Business Intelligence Development Studio (they are not leveraged in any
manner during package execution or available via programming methods). This
implies that all of their information necessary to connect to the target data
store is copied to connections derived from them, which deliver required
functionality at run-time.
In order to create a data source, right-click on the Data Sources Folder in
the Solution Explorer window, displaying the content of the currently modified
project, and select the New Data Source… option to start Data Source Wizard.
On its "Select how to define the connection" page, you are given an
opportunity of leveraging an existing connection or another data source in the
same solution or creating a new data source based on a new connection, (the
fact that you define a data source using the familiar Connection Manager
interface further emphasizes similarities between them). Once this step is
completed, you can alter or fine tune data source properties from its Designer
window (which you invoke by selecting View Designer or Open options from the
context sensitive menu of the data source entry in the Solution Explorer window
– or simply by double-clicking on it). From there, you have the ability to
perform the following actions:
-
edit data source name and connection string. The latter is
defined using the same Connection Manager interface, from which you can
designate an appropriate provider (.NET or native OLE DB provider pointing to a
relational data store) and associated with it connection settings. -
specify (with a checkbox) whether you want to maintain a
reference to another data source within the same solution. Based on the entry
in the listbox that is below the checkbox (located within the same Data Source
reference section of the Data Source Designer window) you can choose between
existing data sources within Integration Services or Analysis Services projects.
Note that this action precludes the ability to modify the connection string
manually (i.e. both options are exclusive), since the decision to maintain
reference to another data source implies that the current connection string is
inherited and can not be directly changed. This is reflected by the fact that
the Connection string entry becomes grayed out as soon as you select the
"Maintain a reference to another data source" checkbox. On the other
hand, clearing the checkbox terminates the inheritance mechanism (as well as
synchronization between two data sources) and allows modifications to the
connection string. -
configure isolation mode, query timeout, and maximum number of
connections for this particular data source (these parameters can be assigned
independently for each data source, regardless of their reference settings).
While data sources provide the benefit of consistent and easily modifiable
configuration of data across multiple packages and projects, their flexibility
is limited, since they offer only straightforward access to all tables and
views in a target data store. If you have more sophisticated requirements (such
as customizing range and type of available objects), you should consider using
data source views instead. Just like data sources, views are defined on the
project (including Analysis Services) level (which allows them to be shared
among multiple packages) but are tied to a specific data source within the same
project (hence can not be copied directly across projects). They designate the
scope of target database objects that subsequently can be used when configuring
SSIS Data Flow Sources and Destinations. However, their functionality extends
beyond simply revealing arbitrarily chosen characteristics of underlying data.
In addition, as part of their configuration (using Data Source View Designer
features), you can also define new relationships between tables, replace them
with named queries, or extend them by adding calculated columns. In addition,
their metadata caching capability facilitates offline package modifications,
without requiring an established connection to the target data store.
To create a Data Source View, launch Data Source View Wizard by selecting
the New Data Source View… item from the Data Source Views folder in the
Solution Explorer window in Business Intelligence Development Studio. First,
you will be prompted to select an existing data source on which the view will
be based. At this point, you can also configure advanced options (by clicking
on the Advanced… command button on the Select a Data Source page of the
wizard), which determines a range of items (relationships and schemas) to be
retrieved from the data source. Your choices will affect a listing of available
objects displayed on the Select Tables and Views page of the wizard. From here,
you can select tables or views you are interested in and (if desired) add all
tables related to them (it is also possible to filter their list based on the
comparison – with wildcard character support – to an arbitrary string). Once
the view is created, you can open and modify it via the designer interface
using Open or View Designer items from its context sensitive menu (or by simply
double-clicking on it) in the Solution Explorer window.
In order for individual SSIS components to take advantage of views, you need
to define connections (within the package where these components reside)
referencing data sources on which the views are based. To accomplish this, once
your data source views are defined, open the package where you intend to use
them and create connections that are based on the data sources from which the
views were derived. At that point, you should be able to point SSIS data source
view-aware components (such as Data Flow OLE DB Source or Destination) not only
to the data sources (via connections that represent these data sources) but
also to their views (via the Connection Manager section of their Editor
windows).
By combining the above-described features of templates, data
sources, and data source views with package configurations, you can
significantly improve the portability of your packages as well as simplify
their deployment and maintenance.
