SQL Server 2005 Integration Services - Templates, Data Sources, and Data Source Views - Part 50

Friday Jun 8th 2007 by Marcin Policht

Part 50 of this series dedicated to SQL Server 2005 Integration Services looks into functionality intended to improve efficiency of managing Business Intelligence Development Studio-based projects.

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 8\Common7\IDE\PrivateAssemblies\ProjectItems\DataTransformationProject\DataTransformationItems 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.

» See All Articles by Columnist Marcin Policht

Mobile Site | Full Site