Export (0) Print
Expand All
Creating Audit Tables, Invoking COM Objects, and More
Exception-handling Techniques
Exploring SQL Server Triggers: Part 2
Deliver User-Friendly Reports from Your Application with SQL Server Reporting Services
SQL Server: Display Your Data Your Way with Custom Renderers for Reporting Services
Updating Data in Linked Servers, Information Schema Views, and More
XML Features in SQL Server 2000
Expand Minimize

Best Practices for Using DTS for Business Intelligence Solutions

SQL Server 2000
 

Trey Johnson and Mark Chaffin
Encore Development

Updated June 2004

Applies to:
   Microsoft SQL Server 2000
   Microsoft SQL Server 2000 Analysis Services
   Microsoft SQL Server 2000 Data Transformation Services (DTS)

Summary: Discover the best practices for using DTS within the Data Warehousing Framework to capture and present data as Business Intelligence solutions. (108 pages)

Contents

Introduction
DTS and the Data Warehousing Framework
Package Design Practices
Guidelines for Inclusion of Metadata in Package Designs
Analysis Services Management Practices
Decision Support Objects within DTS
Creating Partitions
Cloning Partitions
Merging Partitions
Dropping Partitions
Guidelines for Managing OLAP Partitions
Analysis Services Processing Task
Execute on Main Thread
Using DTSRUN to Perform Analysis Services Processing
Programming the Analysis Services Processing Task
Guidelines for Performing Analysis Services Processing with DTS
Practice Summary
Auditing and Error Handling Practices
Fail Package on First Error
Practice Summary
Enhancing DTS Functionality Practices
Conclusion
Appendix A – About the Authors
Appendix B – Code Listing for .NET Custom Task

Introduction

Business Intelligence (BI) solutions such as Data Marts and Data Warehouses rely heavily on tools to migrate data between heterogeneous data sources and support consolidated analytical decision-making. Microsoft SQL Server 2000 Data Transformation Services (DTS) provides the flexibility and advanced functionality to automate the architecture by which operational data is captured and delivered to the end-users of BI applications. The flexibility found within the DTS platform provides for a multitude of approaches to solving the data warehousing requirements of a solution leveraging the Microsoft Data Warehousing Framework.

This white paper explores the best practices that can be leveraged in delivering comprehensive BI solutions via the Microsoft Data Warehousing Framework. The white paper is comprised of six sections: Overview of the Data Warehousing Framework; Package Design Practices; Extract, Transformation, and Loading (ETL) Practices; Analysis Services Management Practices; and Enhancing DTS Functionality Practices. Each best practice section defines the preferred approaches, the guidelines, and the benefits of the use of this practice.

This white paper begins with insight into the Data Warehousing Framework and DTS role within the Data Warehousing Framework. The next section details the best practices for approaching DTS package design, exploring concepts like metadata-driven configuration and modular package development. The white paper details the best practices of extracting and staging source data, transforming and cleansing data in readiness for the production data warehouse, and managing the loading of slowly changing dimensions and fact areas. The practices for performing integrated management of the SQL Server 2000 Analysis Services are then covered with approaches for managing the creation of and processing of OLAP partitions, along with other OLAP Cubes and Dimensions. In the remaining sections, the best practices for monitoring the overall execution, through error handling and auditing, of the DTS package solution architecture and enhancing this architecture through custom programming are explored.

Technologists, familiar with the Data Transformation Services platform within SQL Server 2000, are the primary audience for this white paper. For an overview of the features found within Data Transformation Services, read the Data Transformation Services (DTS) in SQL Server 2000 white paper or consult the Microsoft SQL Server Books Online.

DTS and the Data Warehousing Framework

Overview of the Data Warehousing Framework

Microsoft developed the Data Warehousing Framework as an open, scalable architecture that speeds, simplifies, and reduces the cost of building, managing, and using today's business intelligence applications. The Microsoft Data Warehousing Framework provides all of the benefits of a full-featured, high-performance, integrated data warehousing platform—SQL Server and Microsoft Office—while offering the broadest choice and flexibility to information technology (IT) professionals seeking scalability and specialized applications.

Aa902667.sql_busintbpwithdts_01(en-us,SQL.80).gif

Figure 1. Microsoft Data Warehouse Framework

The illustration above shows the core characteristics of the Data Warehousing Framework.

DTS Role in the Data Warehousing Framework

For its part, Data Transformation Services interacts with all elements of the Data Warehousing Framework. As a provider of services for extracting heterogeneous data from OLEDB and ODBC compliant data sources completely through the transformation and loading of operational data into analytical, multi-dimensional data stores; DTS owns a significant role in the success of these solutions. A core element of making DTS successful within the framework is taking a sound approach to building the architecture of DTS packages for maximum flexibility, the first of our best practices explored in this white paper.

Aa902667.sql_busintbpwithdts_02(en-us,SQL.80).gif

Figure 2. DTS role in framework

Package Design Practices

Delivering a valuable, sustainable, and most importantly, flexible DTS technical architecture for an SQL Server 2000 Business Intelligence solution requires investment in the design considerations for achieving these goals. The concepts of the core best practices for designing DTS packages within BI solutions are explored in this section.

Metadata-Driven Approach

Driving Packages with the Dynamic Properties Task

The Dynamic Properties task, added in SQL Server 2000, is one of the most efficient ways to utilize package configuration information (or metadata) stored outside of the DTS package. This information is read at runtime and the execution is dynamically customized by this task. The task allows property information to be obtained from sources such as INI files, relational DBMS queries and textual data files. Storing the package, or more appropriately, the task, information within an external information source allows for packages to migrate nicely between development and production environments and isolates the changes to the package configuration to text files or DBMS tables. This also paves the way for modular package designs leveraging metadata.

Populating Global Variables via Execute SQL Task

Another method for sourcing package metadata information is to use the Execute SQL task and the output rowset parameter to capture all of the settings for the global variables in a single query in lieu of multiple queries with the Dynamic Properties task.

Use of the Execute SQL task requires that a minimal amount of ActiveX scripting be done to turn the multiple rows of global variable records returned as a rowset into actual global variables. The example below shows the necessary script to turn a three-column rowset (GlobalName, GlobalValue, and GlobalConversion), stored in the global variable GlobalVariablesRowset, into a collection of global variables for the package.

   Dim GlobalRowset, GlobalName, GlobalConversion

   Set GlobalRowset = DTSGlobalVariables("GlobalVariablesRowset").Value

   'Check for GlobalVariable Records within the GlobalRowset
   If GlobalRowset.RecordCount <= 0 Then 
        'Exit the function and report failure
        Main = DTSTaskExecResult_Failure
        Exit Function
   Else
        GlobalRowset.MoveFirst()
      
        Do While Not GlobalRowset.EOF
           GloballName = GlobalRowset("GlobalName").value
           GlobalConversion = GlobalRowset("GlobalConversion").value

           'Dynamically build the VBScript for 
           'assigning a value to the global variable
           'Note the use of the Conversion column 
           'which holds "CStr", "CBool", "CInt"
           EXECUTE( "DTSGlobalVariables(cstr(GlobalName)).Value =" & _
              GlobalConversion & "(GlobalRowset(""GlobalValue"").value)")
           GlobalRowset.MoveNext()
       Loop
   End If

   Set GlobalRowset = Nothing
   Main = DTSTaskExecResult_Success

As this script executes, global variables are either created or updated with the appropriate value and datatype.

Designing the Metadata-Driven Package

The toolset provided by DTS to readily build metadata driven packages makes the rationale for doing so very compelling. As the design process is begun, a logical approach is to look at the categories of packages that are to be developed. Within these categories, the variable parameters apt to change, once the package is in a production environment, should be explored. Developers with a background in object-oriented development might draw a parallel between designing packages driven from metadata and the design of objects (i.e., Packages) that expose properties (i.e., Global Variables) that are used by methods (i.e., Tasks) of the object.

Below is a simplified portion of the package design illustrating the use of the Execute SQL task to retrieve a global variable rowset, the ActiveX Script task to convert the rowset into global variables and the Dynamic Properties task to assign global variables values to other task properties (not depicted) within the package.

Aa902667.sql_busintbpwithdts_03(en-us,SQL.80).gif

Figure 3. Simplified package design

Guidelines for Inclusion of Metadata in Package Designs

The flexibility provided by DTS offers the opportunity to utilize differing but comparable approaches to creating metadata-oriented package designs. Regardless of the approach, there are a few key elements to consider when designing for the use of metadata. These considerations are:

  • As with other architectural design in BI solutions, develop a strategy for using metadata and map this strategy to benefits for doing so. The benefits can address the ease of maintenance, the support for remote configuration of the package, or countless others.
  • For text file based metadata (i.e., INI files or XML files), be certain to reference these files by fully qualified UNCs (i.e., \\MyServer\MyShare\MyINIFile.INI) to make certain of the greatest portability within the network environment.
  • Consider using SQL Server relational tables for global variable information as this approach ensures the overall connectivity to the source or target database and protects against issues like resource locking that can be experienced with file-based resources.
  • Drive settings that are apt to change such as server names, OLAP cubes and source file load paths from metadata.
  • Do not attempt to drive everything from metadata. By overusing metadata, not only will the metadata be unmanageable, often the packages themselves are too.

Employing sound judgment, leveraging the approach discussed and considering these guidelines will build value in the design and construction of practical, flexible metadata driven packages.

Parent/Child Package Approach

Global Variables

Global variables are often an essential element of all package architectures. Global variables provide the means for communication between tasks and act more as parameters to task execution; the more complex an overall package design becomes. However, global variables are not solely intended for communication among tasks within a single package. In fact, the global variables are essential for enabling a design approach referred to as parent/child package (or hierarchical packages) design.

Within the parent/child package approach, the workflow from a package continues through the workflow of a child or subordinate package's execution. A common example would be a parent ETL loading package executing a child OLAP processing package. This example will be explored further in a later portion of the white paper.

Execute Package Task

The Execute Package task provides off-the-shelf functionality for implementing parent/child package designs. A parent package can include an Execute Package task and then identify the storage location and global variables to pass to the child package. At the point when the task executes, subsequently executing the child package, the task can have the descendant package(s) join its transaction.

This task provides for the rapid inclusion of child packages as functionality "building blocks" within the overall design.

Execute Package via Object Model

Developers have leveraged the parent/child package design since the first introduction of DTS in SQL Server 7.0. The means for delivering this functionality came in the form of using COM automation and ActiveX Scripting to pass global variables to and invoke a child package. There are both negatives and positives to this approach. Using COM automation, a package cannot join the parent package's transaction. However, the benefit to using COM automation is there is no requirement to identify the version GUID of the package being executed, as is the case with the Execute Package task.

Designing the Parent/Child Package Hierarchy

To add clarity to the two design approaches for implementing the parent/child package hierarchy, a visual comparison of the two techniques is shown below.

Aa902667.sql_busintbpwithdts_04(en-us,SQL.80).gif

Figure 4. Execute Package Task Properties window

Aa902667.sql_busintbpwithdts_05(en-us,SQL.80).gif

Figure 5. ActiveX Script Task Properties window

Fortunately, with exception of the noted differences, using the approach of executing a package from the Execute Package task or from the ActiveX Scripting yields the same results – one package being executed by another. The goals for designing and developing to a parent/child package hierarchy are often one or more of the following:

  • Reduce workflow complexity within a single package (i.e. make packages easy to read in the design environment)
  • Modularize the design of packages (i.e. ETL packages for Dimension Extract, Dimension Load, Fact Extract, Fact Load, OLAP processing and others) for maintenance efficiencies
  • Manage the execution of multiple packages with dependencies upon each other
  • Desire to share variables between packages
  • Encapsulate specific functionality, driven by metadata, which makes the child package re-usable.

Pursuing more than one of the above design goals is very common for moderate to complex ETL designs within BI solutions.

Guidelines for Parent/Child Packages

While the tools to create parent/child package architectures offer a simple implementation, there are additional guidelines to be considered when exploiting this practice. The most notable guidelines are:

  • Manage the level of nesting among packages and try not to go more than two children deep.
  • Understand the role of the two Failure properties of packages and tasks, respectively "Fail Package on First Error" and "Fail Package on Step Failure".
  • Establish a concurrent usage estimate for re-usable packages; if it is high, store the child package within SQL Server. Doing so eliminates the risk of exclusive file locks (when the package is stored as a structured storage file) being placed upon the child package, thus destroying concurrency.
  • Employ a naming convention that indicates the core function of the package (that is, dimension loading packages begin with "DIM"; fact loading packages begin with "FACT", and so forth).

Practice Summary

Benefits

Incorporating metadata into the DTS architecture and relying upon multiple parent/child packages provides a strong foundation for the BI solution. The most noteworthy of these are:

  • Repeatable deployment

    Using metadata to configure a package means that deployment is as easy as updating the metadata. This is a sound approach when combined with the need to preserve the version (or GUID) of a child package called from an Execute Package task.

  • Parallelism made easy

    Adopting a hierarchical mindset early in the overall design unveils the option of significant degrees of parallelism through multiple threads calling the same package.

  • Maintainability supporting rapid change

    Designing one child package for a particular purpose provides the ability to "upgrade" an element of the enterprise DTS architecture without touching several packages. The net result is a more rapid approach to maintaining packages.

Precautions

The precaution areas for this practice and the subordinate approaches are limited but do exist. The key precaution area is:

Longer initial design

The result of following the prescribed approaches is a longer period to design a package initially because of the dependence on metadata and determining how metadata should be used. However, this doesn't mean that a minor delay does not have its benefits. For the quick prototyping or one-off loading of data, the use of parent/child packages or metadata might be more significant than the process DTS is being used to solve.

The means in which child packages are developed will create either a very manageable or barely manageable architecture depending upon the thought put into the design and the desire to achieve the previously mentioned goals. Following the guidelines and techniques provided, should help achieve a successful implementation of Parent Child packages, building on the metadata driven design. Ultimately, beyond these design "best practice" activities, the solution set to achieving the overall BI solution architecture with DTS is found in the next sections.

Extract, Transformation, and Loading (ETL) Practices

By definition most BI solutions are only as good as the approaches taken to extract, transform and load data into relational data stores. These ETL processes are the pivotal point for success or failure in most BI solutions. DTS as a tool provides the facilities for extracting heterogeneous data, automating the transformation of data, and supporting the end-to-end process of loading data from an OLTP schema to a dimensionally modeled data store. For these reasons, this section covers the best practices for ETL.

Source Extraction and Stage Loading Approach

  • Universal Data Link (UDL) Connection files

    A UDL file is an object outside of DTS. As its name implies the file (*.UDL) provides access to data on any of the system installed OLEDB providers. The UDL file maintains all of the connection information within the file. DTS readily supports the UDL file as a data source for a package connection.

  • Text file destination

    DTS' support for text file destinations provides a convenient standard format for data from source systems to be extracted into a file. Exporting to a file from a source system extract makes the remaining portions of the ETL process easier to manage.

  • Bulk Insert task

    The Bulk Insert task is a functional means of loading data from a text file directly into the associated SQL Server staging table. The benefits of bulk insertion include speed of loading as one of the overriding factors for its use.

Managing Portability through UDLs

As was briefly discussed, configuration metadata provides many key benefits including platform portability. Many organizations engaged in BI solution development provide a development/testing platform and a production platform for use during the project. As is sometimes the case, there may be even more physical servers supporting the solution's development. In addition to server-based machines, developers will often use their own desktop machine as well for development. The end result is a myriad of machines on which the same version of the DTS package needs to run and have its connection information point to appropriately.

Click here to see larger image

Figure 6. Connection and Datalink properties windows (click image to see larger version)

UDL files solve the portability of connection information among a solution's server resources. The graphic below shows both a UDL file being configured and referenced from within DTS.

Selecting the Always read properties from UDL file option will ensure a connection based upon a UDL will reference the connection information found within the UDL file at every execution. This functionality makes the UDL file the "missing link" for metadata based connection information.

Achieving Autonomy through the Text File Export

The approach considered the best practice for performing a source extract is to extract from heterogeneous data sources into a common text file format. Using the combination of an OLEDB or ODBC compliant data source and a delimited destination file, the extraction portion of an ETL process can be executed without mandating the immediate execution of successor transformation and loading processes. Some common design drivers for using this technique are:

  • Source systems generally not hosted on the same server as the BI solution
  • Production schedules between source systems and the data mart have differing windows of availability
  • Multiple geographically dispersed source systems

Benefits to using text files include:

  • Ability to rapidly export data from a source system
  • Immediate determination of data issues when bulk inserting into the staging tables
  • Availability of an offline copy of the data for the purpose of troubleshooting, data analysis and other functions of the BI developer
  • Source system independence = true autonomy

By taking data in the form of a text file, source systems can migrate between relational database platforms or even more realistically just change schemas altogether and the BI solution will be blissfully unaware of any such event. By insuring continued conformance to a text file layout, the burden resides with the updated source system and the extraction package to pull the data into this format. The remainder of the ETL architecture remains unchanged. Using mocked up copies of the text files, allows for the prototyping of data sources before their extraction is complete making possible a more productive development lifecycle.

Staging in the DTS ETL Process

The process of loading data captured in the extract process to relational tables provides a single environment, the stage environment, where data is made ready for the production data mart/data warehouse BI solution. Within the staging environment should reside non-indexed, relational tables that are structurally similar to the text files extracted from the source systems. This similarity should include data types that are the expected data type of columns extracted from the source systems.

Loading text file data into stage tables should be performed with the Bulk Insert task within DTS. Using the Bulk Insert task provides for efficient loading of the tables and also raises the red flag on data that is not conformant to the columns in the staging environment. By raising exceptions during the loading of the staging environment, troubleshooting can begin with the source and not be obscured by an otherwise partially successful load of the production setting.

Guidelines for Extracting and Stage Loading

While the techniques of the approach for extracting data and performing stage loading are not entirely complex. There are some guidelines to ease the overall development of this portion of the solution. These are:

  • Explore the portability of data sources through UDL files. Start with a desktop database like Microsoft Access or use an instance of SQL Server and then move to another SQL Server. If the extracts still work by only changing the UDL files, then the design is a success.
  • Avoid trying to transform data when extracting or loading the staging environment. This puts an unnecessary burden on the source system, extends the duration of the extract, and jeopardizes the validity of the logic if the source system changes.
  • Use the stage as the BI sandbox. Anything goes in this environment especially non-log intensive operations like TRUNCATE TABLE before loading.

Managing the overall design to the concepts presented here will provide a flexible approach to extracting and staging data for the BI solution. Once the data is in the staging environment, data transformation and cleansing can be performed.

Data Transformation and Cleansing Approach

Computed Columns and Checksum Functions

A valuable feature introduced in SQL Server 2000 is the support for computed columns. Computed columns provide the ability to create a column defined by a computation that updates as the other elements of the computation change. Of particular interest in SQL Server BI solutions is the combination of the CHECKSUM() or BINARY_CHECKSUM() functions with the computed column feature.

The CHECKSUM() and BINARY_CHECKSUM() functions provide the ability to pass multiple variables, table columns or an entire row of a table as parameters. The result of the functions will be a checksum for the entire set of passed parameters. The BINARY_CHECKSUM() function differs only slightly in the fact that the values are converted to their binary representation. The binary representation essentially makes the result case sensitive with differing checksums being produced for two occurrences of the same string with a differing case. To best understand the effect of the checksum functions, an example is provided below:

CREATE TABLE [Person] 
(
    [PersonID] [varchar](20) NOT NULL ,
     [LastName] [varchar] (30) NOT NULL ,
     [FirstName] [varchar] (30) NOT NULL ,
     [MiddleName] [varchar] (30) NULL ,
     [PersonChecksum] AS     
         (checksum([PersonID],[LastName],[FirstName],[MiddleName])) ,
     [PersonBinaryChecksum] AS         
         (binary_checksum([PersonID],[LastName],[FirstName],[MiddleName])) 
)

The above DDL creates a Person dimension staging table using both checksum functions as computed columns. Querying a row from the table yields:

Table 1. Query Results

PersonIDLastNameFirstNameMiddleNamePersonChecksumPersonBinaryChecksum
1234-0001SmithJohnEdward-52588501-1292791390

An index can be placed on either or both the PersonChecksum and PersonBinaryChecksum columns to create a Hash Index. Doing so would thus make the data results for the computed columns physical (or materialized) within the table. Once the index is in place there is no need to reference the textual information about the Person when performing a dimensional update, discussed a little later in this Best Practice section.

Computed columns can also support come of the common transformation types which typically occur in the preparation of data for the final BI relational data store.

Types of Transformation

There are a limitless number of transformation types that can exist for data taken from source systems which provide dimension and fact records. Some of the more common transformation types include:

  • String parsing – taking a subset of the string information found within a char or varchar datatype
  • Type conversion – taking the value found within a source column and representing it as a differing data type in the final BI solution (i.e., tinyint value of 1 or 0 converted to a char(1) value of Y or N)
  • Domain lookup – transformation where the value from the source system is used to lookup an alternate value from a data store (i.e., using the natural key for a dimension to lookup the surrogate key for the dimension in the data mart)
  • Numeric conversion – conversion of a number to meet a standardized value across the entire data mart (i.e., multi-national monetary sales are converted into US dollars for analysis)
  • Domain data validation (bounds checking) – transformation where values are checked to see if they are within an acceptable bounds (i.e., sales dollars should be between $20,000 and -$20,000)

An equal number of limitless places exist within the ETL for transforming data. The general categories for these transformation locations are:

  • Outbound transformation – transformation of data as it is extracted from the source system
  • Inbound transformation – transformation of data as it is loaded into the staging area
  • Staging transformation – transformation which occurs in the staging area after the data has been loaded and before it is pushed to production
  • Production load transformation – transformation built into the loading process when data is taken from the staging area and inserted into the Dimension and Fact tables in the production data store

Technologies for Performing Transformations

There are three likely technical approaches to implementing logic for transformation of data within a DTS architecture. The three approaches, in preferred order with benefits of each, are:

Transformation using Transact SQL

Transact SQL can be utilized to apply the various categories of transformation to data stored within Relational staging tables. Typically consisting of UPDATE and DELETE statements, Transact SQL provides many benefits to the transformation process. Some of these are:

  1. High performance manipulation of data within the staging area
  2. Support for complex transformation implementations by making multiple passes through the data
  3. Generally easier for database development staff to implement transformation logic
  4. Closely affiliated with preferred logic for production loading

Transformation using DTS Stock Transformations

DTS provides a number of stock transformations to accomplish many of the tasks for manipulating the data in a BI staging environment. The techniques for using these transformations in conjunction with the staging environment often require multiple stage tables for transforming data in various ways. The key strengths of using DTS transforms are:

  1. High performance manipulation during Outbound/Inbound transformations
  2. Extensible through development of custom transformations in Visual C++
  3. Richly supported within the Package design environment

Transformation using DTS ActiveX Script Transformations

One of the stock transformations offered by DTS is the ActiveX Script transformation. The variety with which this transformation can be used has benefits to the transformation process. The opportunity for using this transformation approach incorrectly also exists. Below is a listing of some of the benefits of using this technical approach.

  1. Support for compound custom logic during the Outbound/Inbound transformations
  2. Support for the inclusion of COM objects during the transformation
  3. Integrates the Multiphase Data Pump feature within DTS

The biggest cautions about this final approach are to not expect the same degree of performance as is offered by Transact SQL and Stock DTS transformations. The overhead of using ActiveX Scripting logic typically produces results that are remarkably slower than performing transformations using the other technical approaches.

Multiple Passes to Transformation

During data transformation, it is typically a good idea to employ transformation by making multiple passes through the original data. SQL Server, and Transact SQL, generally best implements compound transform logic in multiple passes using set based operations. Set based operations provide the facility for manipulating larger data sets more efficiently than a singleton approach, typically based upon cursor logic of processing a row at a time.

Chunking T-SQL Logic for Log Performance

While SQL Server best supports set based operations, the warehousing and transformation of large quantities of data can affect the overall size requirements of the transaction log. A common approach, to lessen the impact on the transaction log when performing logged data manipulations, is to "chunk" the data into smaller subsets. These smaller subsets of data can readily be manipulated with a lesser number of transactions to log and thus a smaller overall transaction log requirement.

Chunking can be performed in a number of different ways within Transact SQL. Generally, a WHILE loop is required and within the loop the subsets are updated using techniques that rely on ROWCOUNTs or WHERE clauses to affect only a subset of the data. Following each iteration of the chunked logic, the transactions associated with this logic are committed allowing SQL Server to free the associated transaction log space. Because the intent is to isolate transactions to each chunk of data being processed, it is not advised that a transaction is begun or committed outside of the WHILE loop.

Consolidation of Transformations

With all of the options for transformation logic, the opportunity to create transforms in multiple portions of the ETL processes exists. While the opportunity does exist, doing so is not considered an element of this best practice. Consolidating transformation logic to Transact SQL operations within the staging area provides the following benefits:

  • Eases maintenance of transformation logic
  • Lessens susceptibility to source system platform changes
  • Minimizes the impact on source systems if the logic is done in the staging area
  • Provides opportunity for creating a repository of transformation functions for use in other BI initiatives

Guidelines for Transformation and Cleansing of Data

The combination of techniques discussed for transforming and cleansing data is likely the means for providing the right BI solution. As these combinations are being considered, there are some guidelines that should provide for the optimal approach. These guidelines are:

  • When using checksum and binary_checksum functions, be sure to perform the checksum comparisons in the same order. A different checksum value will result if column order or parameter order is changed.
  • If using ActiveX Scripting transformations, use the ordinal number of the column in lieu of its name for better performance.
  • Consider chunking as an option even if initial data sizes are small. Chunking provides the easiest means for assuring scalability within the Transact SQL processing during transformation.
  • If possible, be sure to add indexes only after data has been loaded to the staging area to achieve the most optimum performance.

Achieving efficient and effective results transforming the data within the Staging Area provides an eased transition into the loading phase of the ETL process.

Managing Slowly Changing Dimension Loading Approach

Slowly Changing Dimension Types

Within the Business Intelligence industry, there are three types of slowly changing dimensions. These three slowly changing dimension types are:

  • Type I – as data for a dimension member changes, the latest column values overwrite the previous dimension record thus eliminating the history of the dimension member.
  • Type II – as data for a dimension member changes, the latest column values are stored as a new record within the dimension providing multiple occurrences of a dimension member allowing for the history to be preserved.
  • Type III – as data for columns of a dimension member change and if the data mart desires to preserve the last version of that changed column, the original data is moved to a last version column on the dimension record and all new dimension information overwrites the existing columns.
    Note   In general terms and specifically for this white paper, the Type III slowly changing dimension is not being considered.

Multiphase Data Pump

The multiphase data pump is a feature introduced with SQL Server 2000 that provides even greater flexibility to using the DTS data pump to move data between data sources. The Data Pump now supports phases that occur prior to the sourcing of data, during the transformation of a row, after the transformation of a row, at the completion of a batch of rows, after the last data has been read from the data source and at the completion of the data pump operation. These phases are exposed and are capable of having script associated within them through use of the ActiveX Script Transformation.

While the processing of data using ActiveX Script transformations was remarked as the least favorable in transforming large quantities of data, the specific process of loading dimension records is generally less data intensive. Given this, the techniques presented in this section offer a preferred approach to managing dimension change and leveraging the intrinsic features of DTS in doing so.

Global ADO Disconnected Recordsets

While touched upon earlier in the section, Populating Global Variables via Execute SQL Task, a crucial element of this approach is being able to cache the dimension members found within the production data mart. The cache for this data is a client-side disconnected recordset stored within the global variable by the Execute SQL task.

Indexing Disconnected Recordsets

Regardless of the location, indexes provided faster search operations on data. This fact is especially true of the disconnected recordset. In order to place an index on a disconnected recordset, the Optimize property of a field within the fields collection of the recordset object is set to True. The below code snippet shows the exact process of indexing the recordset.

Rs.Fields(<Field Name or Ordinal Here>).Properties("Optimize") = True

BatchUpdate() Disconnected Recordsets

A recordset that has been disconnected supports the updating of its values. An example of doing just this is a part of the Type II dimension change management discussed later in this section. Updating the disconnected recordset would have no value if these updates could not be written back to the connection that originated the recordset. Fortunately, ADO supports the reconnection of the recordset and the recordset object provides a BatchUpdate() method. The BatchUpdate method provides the facility for populating the recordset's source with updates, in a complete fashion, that were done while disconnected.

Managing Change with DTS on Type I Slowly Changing Dimensions

The management of dimension change on Type I slowly changing dimension requires that:

  1. New dimension members are inserted into the production dimension table
  2. Existing dimension members that have one or more changed columns are updated
  3. Existing dimension members that have not changed do not need any modification but can be overwritten to simplify the logic.

These three steps in the loading of the dimension are readily supported by the functions of DTS. DTS provides the Data Driven Query task and Lookups to allow for the conditional inserting, updating or skipping of rows. The illustration below depicts the DTS package workflow for performing the dimensional load.

Aa902667.sql_busintbpwithdts_07(en-us,SQL.80).gif

Figure 7. Connection task

The Configure Connection task is required to change the ARITHABORT option due to the destination dimension table containing a checksum computed column. Specifically, this Execute SQL task executes the statement:

SET ARITHABORT ON

The remainder of the load logic is accomplished in the "Load Dimension" Data Driven Query task. Below is a listing of the transformation logic used to load a Type I slowly changing dimension.

'Declare constants for each column to make 
'the resolution of the column more efficient at run-time
Const ACCOUNT_NUM = 0
...
Const COUNTRY = 9

Function Main()
Dim AccountWK

'Perform Lookup to see if an account exists
AccountWK = _
    DTSLookups("LookupAccountKey").Execute(DTSSource(ACCOUNT_NUM))

DTSDestination(ACCOUNT_NUM) = DTSSource(ACCOUNT_NUM)
...  The same mapping is performed for 10 columns
DTSDestination(COUNTRY) = DTSSource(COUNTRY)

if IsEmpty(AccountWK)  then
   Main = DTSTransformstat_InsertQuery
else
  Main = DTSTransformstat_UpdateQuery
End if
End Function

Managing Change with DTS on Type II Slowly Changing Dimensions

The management of dimension change on Type II slowly changing dimension requires:

  1. New dimension members are inserted into the production dimension table
  2. Existing dimension members that have one or more changed columns are flagged as no longer being the current version and are given an expiration date.
  3. For the changed dimension member, a new record is created to preserve history and it is identified as the current record.

The achievement of these steps is done using a combined technique of comparing the checksum information from the staging and production tables, assessing change and using the Transform Data task to insert new records into the dimension table. One element that is not a feature of the Transform Data task is support for updates. Use of a client-side, disconnected recordset will support the updates necessary in managing the dimension change.

To begin, a package is created with a Transform Data task between two connections. The transformation for the task is then modified to be an ActiveX Script transformation and the transformation code is as follows:

'constants for the COLUMNS
Const PRODUCTID_WK = 0
...
Const CURRENT_RECORD_IND = 20
Const EFFECTIVE_BEGIN_DATE = 21
Const EFFECTIVE_END_DATE = 22
Const CHECKSUM = 23

Dim Conn
Dim Rs
Dim nRowsInserted
Dim nRowsSkipped
Dim nRowsUpdated

Function PreSourceMain()
'Runs at the beginning of the transform task
nRowsInserted = 0
nRowsUpdated = 0
nRowsSkipped = 0

'We want to establish
'an ADO Disconnected Recordset

' Create instance of connection object and then open the
' connection.
Set Conn = CreateObject("ADODB.Connection")
Conn.Open "file name=" & DTSGlobalVariables("gsUDLFile").Value

' Create instance of recordset object and open the
' recordset object against a table.
Set Rs = CreateObject("ADODB.Recordset")

' Setting the cursor location to client side is important
' to get a disconnected recordset.
Rs.CursorLocation = adUseClient

'Load all of the current dimension records
Rs.Open "Select * from <production data mart dimension> " + _
        " where Current_Record_Ind = 'Y' order by productid_nk ASC ", _
         Conn, _
         3, _
         adLockBatchOptimistic

' Disconnect the recordset.
Set Rs.ActiveConnection = Nothing

Conn.Close

If Rs.EOF And Rs.BOF Then
        Rs.Close
        Set Rs = Nothing
Else
        Rs.MoveFirst
        'Put an index on the Disconnected Recordset
        Rs.Fields(PRODUCTID_NK).Properties("Optimize") = True
End If
PreSourceMain = DTSTransformstat_OK
End Function

At the completion of the PreSource phase of the data pump, an indexed, disconnected recordset is created for the purpose of performing updates and leaving the data pump task only to perform INSERT operations. Using the transform data task allows for the use of the fast load option. In most Type II dimensions, the majority of the writing of data is inserting new records and not typically performing large quantities of updates.

The next phase to be examined is the row transform phase, which uses the Main() function for its logic. Within this phase, the index is searched by natural key, the production record's checksum is compared to the staging record's checksum and the maintenance logic described earlier is performed. The code illustrating this process is:

Function Main()
'Runs for each ROW Based Transformation
If Not (Rs Is Nothing) Then
        If Not Rs.BOF And Not Rs.EOF Then
                Rs.MoveFirst
                'Search for the record in the disconnected recordset
                Rs.Find "productid_nk = " & DTSSource("ProductID_NK")
        End If
        
        If Not Rs.EOF Then
                If (Rs.Fields(CHECKSUM).Value) = _
                        (DTSSource(CHECKSUM).Value) Then
                        'Skip the row because it is entirely the same
                        'Exit the function because there is no column
                        'mapping required
                        Main = DTSTransformStat_SkipRow
                        Exit Function
                Else
                        'Mark the row as not current in the disconnected
                        'recordset as the new record has changed
                        'and the old record must be expired
                        Rs.Fields(EFFECTIVE_END_DATE).Value = Now
                        Rs.Fields(CURRENT_RECORD_IND).Value = "N"

                End If
                                        
        End If
End If

'Map the column values as all rows at this point are inserted
...
DTSDestination(PRODUCTID_NK) = DTSSource(PRODUCTID_NK)

'Set the effective start date for this dimension member
'and the effective end date to a VERY future date
'Make this record the CURRENT version
DTSDestination(EFFECTIVE_BEGIN_DATE) = Now
DTSDestination(EFFECTIVE_END_DATE) = "01/01/2075"
DTSDestination(CURRENT_RECORD_IND) = "Y"

Main = DTSTransformStat_InsertQuery

End Function

Through the course of the row transform phase, the Type II changes are maintained. The last essential step is to make sure the updates performed against the client-side recordsets are written to the database. This will happen in the Post Source phase of the data pump. The logic to reconnect the recordset and synchronize the updated records is shown in the following code, the PostSourceMain() function.

Function PostSourceMain()
' Create instance of connection object and then open the
' connection.
Set Conn = CreateObject("ADODB.Connection")
Conn.Open "file name=" & DTSGlobalVariables("gsUDLFile").Value

If Not (Rs Is Nothing) Then
        ' Connect the recordset.
        Set Rs.ActiveConnection = Conn

        'Push all of the updates back to the server
        Rs.UpdateBatch

        Rs.Close
        Conn.Close

        'Cleanup
        Set Rs = Nothing
        Set Conn = Nothing
End If

PostSourceMain = DTSTransformstat_OK
End Function

As the Post Source phase of the data pump completes, all data is successfully updated within the dimension.

The use of the disconnected recordset offers an increase in the performance over the Data Driven Query approach. While the latter approach was used to show Type II dimension change management, a Type I dimension could use this approach as well.

Managing Dimension Change and Loading with Transact SQL

A suitable alternate approach to using the data pump features of DTS would be to use Transact SQL statement to load a dimension from the staging table. For each dimension to be loaded, the execution of two Transact SQL statements should typically provide for the same results as the earlier DTS-centric approaches.

Type I Dimension Logic

To manage the change of a Type I dimension, the first statement would be an UPDATE statement performing an inner join between the staging table and the production table on the natural key column (provided by the source system). The second statement would be an INSERT statement with a NOT EXISTS clause at the end to be certain that only non-existent dimension member records were being inserted.

Type II Dimension Logic

To manage the change of a Type II dimension, the first statement would be an UPDATE statement performing an inner join between the staging table and the production table on the natural key column (provided by the source system). This update statement would set the effective end date and mark the record as no longer the current version where the checksums did not match. The second statement would be an INSERT statement with a NOT EXISTS clause at the end to be certain that only dimension members with no existing "current" records were appended.

Employing this logic within DTS would require the use of an Execute SQL task.

Guidelines for Managing Slowly Changing Dimensions

Outside of transformation logic, the management of dimension change is one of the more logically intense operations of the ETL processing. Considering the approaches presented will provide the essential foundation for loading of dimensions. In conjunction with the approaches provided, here are practical guidelines to assist in implementing the approaches.

  • When using the Data Driven query, be sure to use a separate database connection for the lookup to insure the read and write operations of the data pump are not serialized from using the same connection for performing lookups.
  • Use the checksum or binary checksum functions to dramatically simplify the comparison logic for Type II updates.
  • Store connection information within UDL files so that the reconnection of a recordset can be achieved by using a connection opened on the UDL file. Doing so will limit the potential of hardcoding the connection information within the DTS package and requires that only the path to the UDL file's location is stored as metadata in a global variable.
  • Perform all other data transformations to dimension members prior to attempting the loading to simplify the loading process.
  • Look at SQL Server Books Online documentation regarding the multiphase data pump.

Following the load of all dimensions, the process of loading the fact data for the BI solution is the final step for completing the loading of the relational data store.

Managing Fact Loading Approach

Using Transact SQL Joins to Assign Keys

The most essential part of loading fact data, the additive data elements which exist at the intersection of dimensions, is establishing the relationship of the data as it came from the source system(s) to the already loaded dimension members. Establishing this relationship is as straight forward as performing a join to the production dimension table from the staging fact table on the natural key column(s).

Data Pump Options – Insert Batch Size

One of the primary drivers for using the data pump task is the ability to batch transactions. Modifying the Insert Batch Size option of the data pump enables the batching of transactions. Provided with a value greater than zero, the data pump will perform that specified quantity of inserts in a transactional batch. The batching of transactions is an important element of scalability. This is especially true when loading large quantities of facts and attempting to keep the overall transaction log size requirements small.

Breaking the overall inserts into batches has the same benefits as the approach to "chunking" described in the transformation and cleansing approaches.

Limiting Indexes to Increase Speed

Loading fact data is one of the larger consumers of time in the BI solutions load window. A factor to consider, in the earliest stages of designing the solution, is whether or not the relational data will be queried or if this data is only meant to be a source to Analysis Services OLAP Cube architecture? If the latter is true, the opportunity to eliminate all indexing on fact data exists. Within Analysis Services, a cube can have an optimized schema that allows the cube to process ONLY the fact table's data and does not require joins to dimension tables as well.

The benefit of not having any indexes in place is a substantially two-fold (or more) increase in the loading of data into the fact table. While auditing is discussed later in the white paper, there exists a requirement to index an audit column on the fact table. Doing so allows for the backing out of one or more incremental loads of fact data should anything go awry in the load process.

Process for Loading Fact Records

By performing a LEFT OUTER JOIN between the fact table and the dimension tables, this allows for the accurate representation of missing dimension member keys. Missing dimension member keys can be checked for in the SELECT clause using a CASE statement and the IS NULL evaluation. This check is performed in order to assign a null key value for fact records with missing dimension keys to two special dimension records. These dimension records are the "Invalid" and the "Unknown" dimension members. A sample Transact SQL statement is provided below showing the join between a dimension table in production and a fact table in the staging area.

SELECT  DimensionKey = CASE 
                   WHEN Dim.Key is null AND Fact.NaturalKey is null 
                       THEN 0  /*Unknown Dimension Record Key*/  
                   WHEN Dim.Key is null AND Fact.NaturalKey is not null 
                      THEN -1 /*Invalid Dimension Record Key*/  
                   ELSE Dim.Key END, 
       Fact.Measure1, Fact.Measure2
FROM    StagingDB..Fact LEFT OUTER JOIN  
        ProductionDB..Dimension ON (Fact.NaturalKey = Dim.NaturalKey)

The result of mapping the keys for existing and null values is completely accurate data, reflecting the overall state of data quality, being inserted into the fact table.

The records returned from this join operation can then be loaded into the target fact table using the DTS Data Pump. While it would be possible to use just Transact SQL and an INSERT statement, the ETL would then loose control over how it handled single erroneous records or small batches with erroneous records. With the batch loading facilities of the data pump operations, erroneous data can be isolated to a batch and the remainder of the fact records can be loaded.

Guidelines for Fact Loading

In most ETL designs, the process for loading fact records is less complex than any other aspect of the ETL. The approach presented here offers a solution for loading fact data fully or incrementally and readily supports the loading of multiple fact table partitions for maximum scalability. Considering the elements presented in this approach, here are some guidelines to contemplate when developing production fact loading ETL:

  • If the relational data store includes multiple fact table partitions, each load thread should address a single partition at a time. Try to avoid using conditional partition logic or fragmented fact data insertion to a view.
  • Be sure to have up-to-date statistics on the dimension table indexes either by running the UPDATE STATISTICS statement following the data load or using the "Auto Update Statistics" database option.
  • Because the batch commit size is being used to manage both the size of the batch inserted to the fact table and the grain with which errors are captured, a reasonable balance between these two factors should be established. Using a setting of 0 or 1, causing either a single batch or a batch per row should typically be avoided.
  • Consider loading of data to multiple fact partition tables to support the future pruning of the BI solution. If an operational requirement is to keep 36 monthly partitions available then removing the 37th partition each month is as easy as dropping that table and recreating the partitioning view.

Practice Summary

Benefits

Leveraging the approaches presented for the Extract, Transformation and Loading best practice will result in many benefits for making operational data available through the SQL Server BI platform. The chief benefits of this best practice are:

  • Restartability

    Extracting data to text files, transforming data within the staging area and loading data using the DTS Data Pump provides for the ability to select a portion of this process as the "restart" point should problems exist. By having data in text files, there is no need to diagnose data quality issues within the production source system and more importantly there is no need to re-run the extracting from this source system if the process needs to be restarted.

  • Manageability

    Defining transformations within the staging area and performing key lookups when loading insures that these pivotal tasks of the ETL process are capable of being maintained.

  • Comprehensive load functionality

    Through blending of Transact SQL and the DTS Data Pump features, a loading solution ties together the distinct features of these platforms and allows for a comprehensive approach, leveraging the right technical characteristics of each.

  • Control over reporting data quality

    By indicating the most common data quality issues, like the "Invalid" and "Unknown" dimension members, the DTS ETL solution plays a significant role in making the organization aware of common data quality issues while not jeopardizing the availability of analytical data.

Precautions

The precaution areas for this practice and the approaches are limited but do exist. The key precaution area is:

  • Reporting of Data Quality

    While the reporting of data quality was mentioned as a benefit, organizations with significant dimension or fact data quality issues may perceive the reporting of data in this way as a shortcoming of the BI solution. Managing expectations around the data quality and establishing the perception of the correctness of this approach makes certain that the organization sees the value in recognizing the existence of both quality and inferior data.

Historically, the ETL processes were only concerned with making data available through the relational data mart or data warehouse. Today, with the Data Warehousing Framework, the final destination for data in many BI solutions is the Analysis Services platform. The next section discusses the role of managing Analysis Services partitions as part of the DTS BI solution infrastructure.

Analysis Services Management Practices

The focus of this section is on the management and processing of Analysis Services dimensions, cubes, and partitions. The techniques portrayed here are a subset of the many approaches for managing Analysis Services from both DTS and other custom applications. Analysis Services provides multiple interfaces for programmatically managing the structure of the OLAP database schema and the functions of the analysis server. Elements, such as managing aggregations on partitions (including usage-based optimizations), dynamically controlling access to OLAP objects through security roles, performing an archival of an OLAP database, and managing the lifecycle of other OLAP objects such as linked cubes, are all accessible through these interfaces and worthy of automation within the Data Warehousing Framework (not to mention their own white paper). This section focuses on the role of Decision Support Objects, one Analysis Services interface, and the DTS Analysis Services' Processing task (built on top of Decision Support Objects) as two technologies to begin automating the management of Analysis Services.

Decision Support Objects Within DTS

To achieve even greater performance and scalability in mid-range to large-scale business intelligence solutions leveraging the Analysis Services platform, one should consider the management of multiple OLAP partitions within individual OLAP cubes. Further, if the decision has been made to partition relational data, the choice of creating and subsequently managing OLAP partitions is really a foregone conclusion. As with all of the other techniques and practices described, DTS can play a significant part in correlating relational and multi-dimensional data stores and managing the OLAP availability of this data through the Analysis Services management processes of creation of, cloning, merging, and dropping OLAP partitions, as part of an overall BI architecture. Along with DTS, a main contributor to this functionality is a collection of COM libraries of SQL Server 2000. These libraries, the Decision Support Objects or DSO, are installable individually as an option of the Analysis Services installation or by default as part of the full installation of the Analysis Services component of SQL Server 2000.

DSO is the COM interface to the Analysis Services Management functions. By using DSO and DTS, many of the functions from the Analysis Manager user-interface are available programmatically within ActiveX Script and other programming languages supporting COM automation. To provide a foundation for managing partitions with the Decision Support Objects and DTS, the portions of the DSO object model hierarchy necessary to achieve this approach are presented below.

Aa902667.sql_busintbpwithdts_08(en-us,SQL.80).gif

Figure 8. DSO with DTS

Creating Partitions

The process of creating partitions is one of the more common scenarios for using DSO within DTS. Automating the OLAP partition creation process can allow the Analysis Services cube and multi-dimensional data store to evolve with the relational data in the BI solution. The driver for using DTS to create a new OLAP partition is often the need to support managing the OLAP schema dynamically in response to the changes in the relational data store.

The below script can be placed within any ActiveX Script object (typically used in an ActiveX Script task) and can be used to create an OLAP Partition.

Function CreatePartition (strServer, strDabase, strCubeName, strPartition)
Dim objServer
Dim objDB 'A DSO.MDStore Object
Dim oCube 'A DSO.MDStore Object
Dim oPart 'A DSO.MDStore Object

Set objServer = CreateObject("DSO.Server")
objServer.Connect(strServer)

The first step, above, was to instantiate a local server object variables from the DSO object model. This object will be used to check for the existence of the OLAP database, cube and partition and create the OLAP partition if necessary.

If objCube.MDStores.Find(strPartition) Then
    Set objPart = objCube.MDStores(strPartition)
End If

If objPart Is Nothing Then
  'Add new partition
  Set objPart = objCube.MDStores.AddNew(strPartition)
End If
Set oPart = Nothing
Set oCube = Nothing
Set oDB = Nothing
Set oServer = Nothing
CreatePartition = True

End Function

By supporting the creating of partitions dynamically, the OLAP cube grows to accommodate new data as needed, rather than the alternative of pre-allocating unnecessary partitions that could impede or cloud the user's interaction with the management functions of Analysis Services.

Cloning Partitions

A cloned partition, as the name implies, is a partition that is a copy of a pre-existing OLAP partition. Cloning partitions is important when programmatically managing partitions that have underlying aggregations. As the overall partition management approach is explored more, the relevance of partition cloning will become all the more clear. The first step, however, to explaining the benefits of cloning partitions, with DSO and DTS, is to explore an example of how to clone an existing partition within ActiveX Script in DTS.

Function ClonePartition(strServer, strDabase, strCubeName, _
                       strPartition, strBasedOnPartition, strSliceValue)

Dim objServer
Dim objDB 'A DSO.MDStore Object
Dim oCube 'A DSO.MDStore Object
Dim oPart 'A DSO.MDStore Object
Dim objClonePart 'A DSO.MDStore Object
Dim objSourcePartDimension 'A DSO.Dimension Object
Dim objSourcePartLevel 'A DSO.Level object
Dim lngDim
Dim lngLev

Set objServer = CreateObject("DSO.Server")
objServer.Connect(strServer)

'Add the partition and then clone it
Set objPart = oCube.MDStores.AddNew(strPartition)

'Copy the Partition to get its basic structure
objClonePart.Clone objPart 

If oCube.MDStores.Find(sBasedUponPartitionName) Then
    Set oClonePart = oCube.MDStores(strBasedOnPartition)
Else
    Exit Function
End If

'Loop through the levels purging each slice value from the newly 
      cloned partition
'As the slice value will need to be rebuilt
For lngDim = 1 To objSourcePart.Dimensions.Count
    Set objSourcePartDimension = objSourcePart.Dimensions(lngDim)
  
    For lngLev = 1 To objSourcePartDimension.Levels.Count
        Set objSourcePartLevel = objSourcePartDimension.Levels(lngLev)
        objSourcePartLevel.SliceValue = ""
    Next
Next


set objSourcePartDimension = nothing
set objSourcePartLevel = nothing

At this point the cloned partition has been created and its slice information (the filtering information) has been purged. Without any slice information, the partition will load all of the data from the fact table, which is not what is desired. A new slice value needs to be applied to the cloned partition and the code listing below does just that.

'Apply the new slice value
'Assumes Slice Value is fully qualified (i.e. "Calendar=All 
     Years.1997.1.January.3" for January 3rd, 1997)
call ApplySliceValue(objPart, CStr(strSliceValue)) 

set objClonePart = nothing
set objPart = nothing
set objCube = nothing
set objDB = nothing
set objServer = nothing
End Function

Function ApplySliceValue(pobjPart, pstrSliceValue)
Dim strSliceValue
Dim strDimensionName
Dim lngStringPosition
Dim objDimension
Dim objLevel
Dim astrLevelSlices()
Dim lngLevel

ApplySliceValue = False
  
 If pstrSliceValue = "" Then
      Exit Function
 End If
 
'Assumes Slice Value is fully qualified 
'(i.e. "Calendar=All Years.1997.1.January.3" for January 3rd, 1997)
 lngStringPosition = InStr(1, pstrSliceValue, "=")
 
 If lngStringPosition > 1 Then
   strDimensionName = Trim(Mid(pstrSliceValue, 1, lngStringPosition - 1))
 End If
 
 If strDimensionName = "" Then
    Exit Function
 End If

  If Not pobjPart.Dimensions.Find(strDimensionName) Then
    Exit Function
  End If
    
Set objDimension = pobjPart.Dimensions(strDimensionName)
strSliceValue = _
Trim(Mid(pstrSliceValue, lngStringPosition + 1, _
 Len(pstrSliceValue) - (lngStringPosition)))

astrLevelSlices = Split(strSliceValue, ".")

For lngLevel = LBound(astrLevelSlices) To UBound(astrLevelSlices)
        If objDimension.Levels.Count - 1 < lngLevel Then
            Exit Function
        Else
            Set objLevel = oDimension.Levels(lngLevel + 1)
            objLevel.SliceValue = astrLevelSlices(lngLevel)
        End If
Next
ApplySliceValue = True
End Function

The approach to cloning partitions is somewhat similar to the approach for creating partitions with a few exceptions. In creating a partition, a check is performed to see that no other partition of the same name exists. In cloning a partition, a check is performed to make sure that the other partition does exist. Additionally, the slice information is generally updated for the cloned partition to avoid duplication of data within the OLAP cube.

The result of cloning a partition is two partitions are of the same structure, with differing slice values, allowing for their assimilation, or Merging, in the future.

Merging Partitions

While the partition management approach has focused on creating and cloning partitions to dynamically grow alongside of the relational data store, there is an equally compelling reason to Merge partitions once they are no longer the analytical "hot spots" within the cube. DSO provides the capability to merge two partitions of the same structure with the same or no aggregations. DTS makes the process of achieving this within BI solution architectures relatively easy, programmatically.

The example script below merges two partitions, a source and a target partition into each other.

Function MergePartitionsbyName(      strDatabase, _
                               strCubeName, _
                               strSourcePartition, _
                               strTargetPartition, _
                               objServer)

Dim objDB 'A DSO.MDStore
Dim objCube 'A DSO.MDStore
Dim objSourcePart 'A DSO.MDStore
Dim objTargetPart 'A DSO.MDStore
Dim objSourcePartDimension 'A DSO.Dimension Object
Dim objSourcePartLevel 'A DSO.Level object
Dim objTargetPartDimension 'A DSO.Dimension Object
Dim objTargetPartLevel 'A DSO.Level object
Dim lngDim
Dim lngLev

MergePartitionsByName = False



'Checking if the database is valid
If objServer.MDStores.Find(strDatabase) Then
    Set objDB = objServer.MDStores(strDatabase)
Else
    MergePartitionsByName = False
    Set objServer = Nothing
    Exit Function
End If

'Checking if the cubename is valid
If objDB.MDStores.Find(strCubeName) Then
    Set objCube = objDB.MDStores(strCubeName)
Else 
    Set objDB = Nothing
    Set objServer = Nothing
    MergePartitionsByName = False
    Exit Function
End If

'Checking if the partitions exist
If objCube.MDStores.Find(strSourcePartition) Then
    Set objSourcePart = objCube.MDStores(strSourcePartition)
Else              
            Set objCube = Nothing
            Set objDB = Nothing
            Set objServer = Nothing
            Exit Function
End If
    
If objCube.MDStores.Find(strTargetPartition) Then
    Set objTargetPart = objCube.MDStores(strTargetPartition)
Else
            Set objCube = Nothing
            Set objDB = Nothing
            Set objServer = Nothing
            Exit Function
End If 

Under DSO, partitions with differing slices cannot be merged together. This is overcome programmatically by first removing the slices from each partition, merging them, and then reapplying an applicable slice value.

'Loop through the levels purging each slice value from the source
'As the slice value will need to be rebuilt
For lngDim = 1 To objSourcePart.Dimensions.Count
    Set objSourcePartDimension = objSourcePart.Dimensions(lngDim)
  
    For lngLev = 1 To objSourcePartDimension.Levels.Count
        Set objSourcePartLevel = objSourcePartDimension.Levels(lngLev)
        objSourcePartLevel.SliceValue = ""
    Next
Next


'Loop through the levels purging each slice value from the target
'As the slice value will need to be rebuilt
For lngDim = 1 To objTargetPart.Dimensions.Count
    Set objTargetPartDimension = objTargetPart.Dimensions(lngDim)
  
    For lngLev = 1 To objTargetPartDimension.Levels.Count
        Set objTargetPartLevel = objTargetPartDimension.Levels(lngLev)
        objTargetPartLevel.SliceValue = ""
    Next
Next
set objSourcePartDimension = nothing
set objSourcePartLevel = nothing
set objTargetPartDimension = nothing
set objTargetPartLevel = nothing

objTargetPart.Merge strSourcePartition

'Apply the new slice value
'Assumes Slice Value is fully qualified (i.e. "Calendar=All 
      Years.1997.1.January.3" for January 3rd, 1997)
call ApplySliceValue(objTargetPart, CStr(strSliceValue)) 

objCube.Update
objServer.Refresh

MergePartitionsByName = True

'Perform Cleanup
Set oSourcePart = Nothing
Set oCube = Nothing
Set oDB = Nothing
Set oServer = Nothing
Exit Function

Merging partitions through DTS and DSO solves the architectural dilemma of managing partition explosion, the increase in numbers of partitions over time, and establishes a clear path for pruning your OLAP data through the dropping of partitions.

Dropping Partitions

While the approach reported here is about dropping OLAP partitions using DSO and DTS, the approach's mantra is solving the challenge of sustaining the availability of data, which is meaningful to the users of the BI solution, and eliminating the data, which is no longer analytically viable. As a result, the task of dropping partitions often coincides with the removal or pruning of old, meaningless fact data from the relational data store, which hopefully the DTS architecture controlled, as well.

Dropping a partition is actually one of the easier programmatic operations with DSO. The actual code to drop a partition is:

Function DeletePartition(strDatabase, _
                               strCubeName, _
                               strPartition, _
                               objServer)

Dim objDB 'A DSO.MDStore
Dim objCube 'A DSO.MDStore
Dim objPart 'A DSO.MDStore

DeletePartition= False

'Checking if the database is valid
If objServer.MDStores.Find(strDatabase) Then
    Set objDB = objServer.MDStores(strDatabase)
Else
    DeletePartition= False
    Set objServer = Nothing
    Exit Function
End If

'Checking if the cubename is valid
If objDB.MDStores.Find(strCubeName) Then
    Set objCube = objDB.MDStores(strCubeName)
Else
    
    Set objDB = Nothing
    Set objServer = Nothing
    DeletePartition= False
    Exit Function
End If

'Checking if the partitions exist
If objCube.MDStores.Find(strPartition) Then
    Set objPart = objCube.MDStores(strPartition)
Else
                       
            Set objCube = Nothing
            Set objDB = Nothing
            Set objServer = Nothing
            DeletePartition = False
            Exit Function
End If

'Checking if the partition exists
If objCube.MDStores.Find(strPartition) Then
    If objCube.MDStores.Count = 1 Then
        'Cannot delete the last partition of a cube
            Set objCube = Nothing
            Set objDB = Nothing
            Set objServer = Nothing
            DeletePartition = False
            Exit Function
        End If
    End If
     objCube.MDStores.Remove (strPartition)
End If

objCube.Update

DeletePartitionbyName = True

'Perform Cleanup
Set objPart = Nothing
Set objCube = Nothing
Set objDB = Nothing
Set objServer = Nothing
Exit Function

Automating the dropping of partitions with DSO and DTS is the last element in establishing absolute control of the programmatic management of the overall architecture, providing for a sophisticated, hands-free infrastructure for delivering the SQL Server BI Solution.

Guidelines for Managing OLAP Partitions

There are a few core requirements of utilizing DSO with DTS to manage OLAP partitions within Analysis Services. Here are some guidelines for satisfying these requirements:

  • The SQL Server Agent service account should be a domain account. Particularly, if the DTS packages intend to manage Analysis Services installed on a machine outside of the context of the local machine where the SQL Server Agent is running.
  • The SQL Server Agent service account whether found on the local machine or on the domain, must be a member of the Analysis Server's local "OLAP Administrators" group prior to executing a DTS package leveraging DSO.
  • During the initial phases of a BI solution, it is very likely that merging partitions will be impossible due to usage based optimizations and other initial OLAP design strategies for increasing the performance being implemented.
  • Use DTS to prevent the scenario of orphaned OLAP partitions with no underlying relational data to support them. As relational data goes away, so too, should OLAP data.

For further information on managing analysis services, see the For More Information section at the end of the white paper.

Analysis Services Processing Task

A key element in integrating DTS with Analysis Services is the processing of OLAP objects, namely dimensions, cubes and partitions, from a DTS package. Fortunately, a stock task exists within DTS for just this purpose. The Analysis Services Processing task provides the ability to process OLAP objects on any accessible Analysis Server.

The existence of this task provides DTS the ability to not only perform traditional ETL but also extend the loading from the relational data store to the OLAP multi-dimensional data store. Lesser known about the Analysis Services Processing task is the fact that it was written using Visual Basic 6.0 and the custom task interfaces which DTS provides via its own component object model.

Execute on Main Thread

The DTS developer familiar with the Analysis Services Processing task has probably discovered that the Workflow Properties for this task differ from most other "stock" DTS tasks, when the task is added to a package. Most notably, the fact that the "Execute on main package thread" property is set to true, as is shown below, is an indicator that the task was developed using a language not supporting the Free Threaded threading model (or an Apartment Threading model), in this case Visual Basic 6.0.

Aa902667.sql_busintbpwithdts_09(en-us,SQL.80).gif

Figure 9. Workflow Properties window

Because DTS utilizes free threading in marshalling resources, tasks that do not support this model must run on the main thread of execution to work properly. "Working properly" means integrating within the free threading found in, as well as not introducing instability into, the DTS environment. This is also true of any child package, which contains and executes the Analysis Services Processing task. In the Parent/Child package scenario, the workflow properties for the Execute Package task in the parent package would need to be set, manually, to execute the child package on the main thread of execution. Doing so would thus require that all concurrent workflows in the child package become serialized on a single thread.

Using DTSRUN to Perform Analysis Services Processing

DTSRun, a command prompt utility provided with SQL Server 2000, provides the ability to execute a DTS package from the command line. Further, command line arguments allow for the package to be passed global variables at the start of execution. The dtsrun.exe is the most common means of scheduling the execution of a DTS package. An alternate utility, DTSRunUI or dtsrunui.exe, provides a graphical environment from which the dtsrun command line with arguments can be built.

While DTSRun is typically used to execute packages as job steps in the SQL Server Agent, its functionality can also be combined with the Execute Process task in DTS to provide an entirely new Win32 Process within which a new thread of execution can be established. The image below illustrates combining the generic Win32 process execution capabilities with the dtsrun utility to launch and OLAP processing child package.

Aa902667.sql_busintbpwithdts_10(en-us,SQL.80).gif

Figure 10. Execute Process Task Properties window

In this case the actual command line for the dtsrun utility was:

DTSRUN /F "C:\olapprocess.dts" /L "c:\olapprocess.log" 
   /A "OLAPDBName":"8"="FoodMart 2000" 
      /A "CubeName":"8"="Sales" /A "PartitionName":"8"="Sales 1997" 
         /A "ServerName":"8"="Localhost" 
            /A "FactTable":"8"="sales_fact_1997" 
               /A "ProcessOption":"8"="0"

It is important to note that this command line includes global variables which designate the Analysis Server where the processing will occur, the OLAP Database on the OLAP Server, the OLAP cube within the OLAP Database, the cube's partition, the fact table for the partition and the processing option. The /A command line arguments provide the global variable name-type-value pairings necessary to execute an encapsulated OLAP processing package. The correlation between these command-line elements, the package, and the underlying tasks is depicted below.

Aa902667.sql_busintbpwithdts_11(en-us,SQL.80).gif

Figure 11. Executing encapsulated OLAP processing package

By executing the depicted package using DTSRUN, the single threading requirements for the Analysis Services processing task and the Execute Package task are overcome. This is due to the main DTS package executing the Execute Process task creating an independent Win32 process space with its own threading. Within the independent Win32 process space the entire OLAP processing package executes. This approach allows for the processing of multiple OLAP partitions or objects from the same root DTS package, through new DTS Package process spaces instantiated by multiple concurrent Execute Process tasks.

Programming the Analysis Services Processing Task

The Analysis Services Processing Task exposes properties that allow for the dynamic configuration of this task during the package's execution. The following properties are capable of being modified:

DataSource

The relational datasource, defined in the OLAP Database, for the object being processed.

FactTable

The relational fact table, which is the source for the cube or partition

Filter

The filter to apply when processing the OLAP object

IncrementallyUpdateDimensions

Whether or not to process dimensions incrementally when processing the OLAP cube or partition object. The acceptable values are 1=Yes or 0=No.

ItemType

Type of OLAP object being processed. The acceptable values are 1=OLAP Database, 4=Cube, 7=Partition and 9=Dimension.

ProcessingOption

The manner in which the designated OLAP object should be processed. The acceptable values are 0=Full Process, 1=Refresh Data and 2=Incremental Update.

TreeKey

The hierarchy for the OLAP server, database, and object being processed.

With a general definition of the various properties, ActiveX Script can be used to set global variables. These global variables will in turn be referenced by the Dynamic Properties task to configure the Analysis Services Processing task. Below is an example of an ActiveX Script for establishing the accepted TreeKey formats for processing a partition.

Dim strTreeKey 'The hierarchy of the OLAP Object to Process
Dim strServerName 'The OLAP Server from the Global Variable
Dim strOLAPDBName 'The OLAP Database from the Global Variable
Dim strCubeName 'The OLAP Cube name from the Global Variable
Dim strPartition 'The OLAP Partition name from the Global Variable

strServerName = DTSGlobalVariables("ServerName").Value
strOLAPDBName = DTSGlobalVariables("OLAPDBName").Value
strCubeName = DTSGlobalVariables("CubeName").Value
strPartitionName = DTSGlobalVariables("PartitionName").Value

'Build the hierarchical treekey for a partition
'ServerName\DBName
strTreeKey = strServerName & "\" & strOLAPDBName & _
             "\CubeFolder\" & strCubeName & "\" & strPartitionName

'Place the TreeKey in the Global Variable
DTSGlobalVariables("TreeKey").Value = strTreeKey

The above script, executed within an ActiveX Script task, would be followed by a Dynamic Properties task to assign the global variable, TreeKey, to the Analysis Services Processing task's TreeKey property. The Analysis Services Processing task would then use this property to process the object as part of the encapsulated OLAPProcess package mentioned earlier.

Guidelines for Performing Analysis Services Processing with DTS

While there are several ways to integrate Analysis Services and DTS, there are some very specific guidelines that should be followed to make the most of the processing approach presented.

  • DSO should be used when processing is not the primary objective. Beyond partition management, several other non-processing functions can be undertaken, including creating local cubes, establishing security roles, changing data sources for connections, and table sources for dimensions and partitions.
  • Invest in using the stock Analysis Services Processing task whenever you can. Re-inventing the wheel for Analysis Services processing with other approaches like DSO is not the ideal approach.
  • Do not overlook the power of manipulating the properties for the Analysis Services Processing task.
  • Use the Dynamic Properties task and global variables to manipulate the Analysis Services Processing task. Otherwise, programmatically manipulating the task through the DTSPackage object model will require the task's properties() collection be referenced by ordinal value.
  • Change the Dynamic Properties task's workflow properties to execute on the main thread otherwise EXCEPTION messages will result.
  • When in doubt on how to program the Analysis Services Processing task, look to the Disconnected Edit feature of DTS to help learn the task's object model.
  • To achieve parallelism in partition processing, an optimized cube design should be in place so as to not put a load on the relational data store when extracting data.

Following these guidelines will make certain that the approach to performing Analysis Services Processing is successful.

Practice Summary

Benefits

Managing the Analysis Services platform from within DTS as a best practice has many benefits. The most noteworthy of these are:

Repeatable extensibility

The practice extends the ETL processing to the presentation tier of the BI architecture, providing a repeatable approach for achieving the goal of making meaningful, timely business analytics available for consumption directly following the load of the relational data store.

Success through autonomy

Processing cubes, partitions and dimensions in a programmatic fashion establishes the foundation for an independent approach to loading the data mart with no cross data mart or BI solution dependencies.

Automation without error

The approaches explored in this practice showcased the potential of automating less than simple processes. Relying on the only other approach for managing Analysis Services, the administrative user, invites more risk to the process than most people are comfortable with taking in a production system.

Precautions

The precaution areas for this practice and the approaches are limited but do exist. The key precaution area is:

Increased Availability

The speed with which analytical data in the BI solution can be delivered through automated Analysis Services processes puts a strong requirement on creating the right procedures in the relational data store's loading to make certain less than desirable data does not get to the OLAP presentation elements of the solution.

Auditing and Error Handling Practices

Intrinsic Auditing Approach

Within DTS, there exist many ways to audit the package and to handle errors that may arise. The first way discussed is using the native or intrinsic auditing and error handling capabilities built in to DTS. These are the easiest and quickest methods to implement, and they provide adequate auditing functionality for most projects.

Logging Options within the Package

DTS has the ability to automatically log package-level events to a SQL Server database without having to save the package to the repository (in previous versions, this was a requirement). The BI developer can use DTS package auditing to check the success or failure of any package. They can also check which tasks within these packages executed successfully, failed or did not execute by checking log information stored in the MSDB database in the sysdtspackagelog and sysdtssteplog tables. Each time the package is executed, a new series of records are added to the log tables so that the entire history of the package can be recorded. In addition, each version of the package's execution history is maintained in these tables, as long as logging is enabled.

To enable logging, the package's Package Properties dialog should be opened and the Logging tab chosen.

Aa902667.sql_busintbpwithdts_12(en-us,SQL.80).gif

Figure 12. Logging tab of the DTS Package Properties window

One key is to make sure that that authentication is set correctly with respect to the planned execution of the package. If the plan is to execute the package using SQLAgent, the SQLAgent authentication must have sufficient read/write rights in the msdb database. Otherwise, SQL Server authentication can be used to log the errors.

Using Data Lineage for Logging

Lineage information provides the means for determining the source of data, be it a table and column in a relational schema, or the package that caused the data to be recorded. Through data lineage, we have unique insight into the definition of data elements within schemas, also known as metadata, and their utilization in packages. Through the recording of this lineage information, provided as variables, we can accurately track how the schema elements are delivered to targets through DTS packages, the workhorses of data extraction, transformation and loading. This type of information provides insight into data issues resulting from changes in our package architectures.

Lineage in Two Forms

Lineage takes on two distinctly different forms in DTS. DTS implements column level lineage (also referred to as Catalog metadata) through the scanning of schema information from OLE DB compliant data sources at the time of transformation. The Catalog information is persisted for later reference within SQL Server and Meta Data Services.

The second form of lineage in DTS is that of a package execution, each time a package executes, and if the feature options are chose, DTS makes lineage identifiers for the particular package available to tasks.

Global Lineage Variables

Lineage variables, which relate to a package's execution, are available within the global variables of the DTS package. These global variables are made available by selecting the Advanced tab of the Package Properties dialog, and then choosing the Show lineage variables as source columns option. The global variables are named DTSLineage_Short, an integer representation of the package lineage and DTSLineage_Full, a GUID (globally unique identifier) representation of the package lineage, or the current execution.

Note   A DTS Package doesn't need to be stored within Metadata services to leverage these global variables.

Lineage and Auditing

Lineage information is captured at the time a package execution is logged, as well as when steps execute. This information is persisted in the system tables of the MSDB database. The audit tables, where this information is found as both a GUID and integer, are sysdtspackagelog and sysdtssteplog.

Including Logging Information in your Transforms

If Show Lineage variables as source columns is enabled, when the data transformation task is created, both DTSLineage_Full and DTSLineage_Short will appear as source columns. These variables can be assigned as columns of data to be inserted or updated along with the other columns in order to track each row's data lineage. The illustration below depicts using the logging global variables in this way.

Aa902667.sql_busintbpwithdts_13(en-us,SQL.80).gif

Figure 13. Transform Data Task Properties window

Intrinsic Error Handling Approach

DTS has the built-in ability to perform handling and logging of errors within the package. Any errors that occur during the package's execution can be passed to the Event Log or to an external text file for logging. In the Event Log, any failure is logged with a source of 'DataTransformationServices' and a type of 'Error'. A sample error event is shown below.

Aa902667.sql_busintbpwithdts_14(en-us,SQL.80).gif

Figure 14. Event Properties window

In the Description text box, a great deal of information can be gleaned about when, where, and why the error occurred. It is important to note that DTS uses the internal name (for example, DTSStep_DTSActiveScriptTask_1) of the step during which the error occurred, and not the English description the developer may have given the task.

By using the Event Log for error logging, an external application can monitor the log for these types of errors and alert the correct support personnel. The log entries could also be used to take corrective action if the error is resolvable programmatically.

Fail Package on First Error

By default, DTS packages will always complete with a successful status, regardless of any error encountered. However, DTS does have the ability to halt execution of any package if an error is encountered and not explicitly handled during any step. This behavior can be manipulated with the Fail Package on First Error setting.

To enable package-level error handling, the package that is to be audited should be opened, the package properties dialog viewed, and on the logging tab the "Fail package on first error" checkbox enabled. At that point, when an error occurs, DTS will return a failure return code. Doing so enables external calling applications like SQLAgent or even an MS DOS batch file, to register when a package fails and take some other action. It also allows the Execute Package task to handle failures of child packages during their execution.

Fail Package on Step Failure

In some cases, the developer may want the entire package to fail if one or only a select few steps fail, but to continue if any others fail. For example, if a step is expected to delete a source extract file, but the file doesn't exist, the step will fail, but the developer may want the package to continue. In this case, the developer can explicitly set only the steps' failures that would trigger an entire package failure.

To enable step-based error handling, right-click the task. On the shortcut menu, click Workflow, and then click Workflow Properties. On the Options tab, select the Fail package on step failure checkbox as shown in Figure 15.

Aa902667.sql_busintbpwithdts_15(en-us,SQL.80).gif

Figure 15. Workflow Properties window

Both of these methods for handling errors are included in DTS and both handle exiting the package when a step fails, but many times, the BI solution may want to fix the situation and continue processing. This is where handling errors with workflow management comes in.

Workflow Error Handling

DTS comes with three types of built-in workflows: On Completion, On Success, and On Failure.

  • On Completion

    The On Completion workflow ensures that subsequent tasks will be executed regardless of the preceding task(s) outcome. This really means that the error is really being ignored and not really handled. This may be the appropriate workflow in some cases, but in most cases, the BI solution may want to handle the error more explicitly.

  • On Failure

    The On Failure workflow is followed whenever a task fails for any reason and neither Fail package on first error nor Fail package on step failure is set. If either is set, they both take precedence over any further activity in the package, and thus, the On Failure task is never executed.

Choosing the Right Combination

One of the powerful elements of DTS is the capability to manipulate its object model using an ActiveX Script task. Since many of the DTS packages in the BI solution are originally invoked from external applications, each package should return the correct return code. The only way to manage this, but still have some control over logging and handling is to take advantage of the object model.

If the solution design uses an error-handling task to manage errors from another task, but the desire is to force the DTS package to return an error code, the following code can be added to the error handling ActiveX Script task to do just that.

     Set oPackage = DTSGlobalVariables.Parent
     oPackage.FailOnError = True
     ...
     Main = DTSTaskExecResult_Failure

Note that the result of the ActiveX Script's Main entry function is set to return failure. If the package's FailOnError property is set to True, the package will end at the completion of this task and will properly return status to its calling program. The disadvantage of using this method is that any logging will reflect that there are two errors that caused the package to fail: the error handling function and its precedent task.

Incorporating this code logic lets the overall solution combine the two most useful methods for handling errors.

Guidelines for Intrinsic Error Handling

While there are several ways to manage errors that occur during a package's execution, there are some very specific guidelines that should be followed to make the most of the processing approach presented.

  • Use external error handler task or stored procedure to minimize changes to existing packages and to modularize code and tasks.
  • Use separate workflows to manage On Failure workflows by designing a separate error-handling task for each of the primary work tasks.
  • Ensure that the error-handling task returns DTSTaskExecResult_Failure and that the Package is set to fail on the first error if the package is being called from SQLAgent, DOS batch files, or any other application that expects a return code.

Following these guidelines will make certain that the approach to managing error handling is successful.

Taking the Custom Approach

Custom Auditing Approach

If the package architecture requires a more complex series of tasks to occur for either auditing or error handling, the flexibility of DTS allows for the replacement of its intrinsic functionality with custom capabilities. This portion of the best practice, will discuss how to customize DTS packages to take advantage of DTS' logging extensibility.

Audit the Package as Part of a Job

DTS Intrinsic Script Object – DTSPackageLog

If the intrinsic auditing is not adequate, DTS exposes an intrinsic scripting object for logging, the DTSPackageLog. This object can be used in the same context as the DTSGlobalVariables scripting object. There are two methods exposed for writing information to the logging destination: WriteStringToLog and WriteTaskRecord. Both can be used to add additional information to the log, with WriteTaskRecord allowing for the adding of an error code to the log. An example of how to use this scripting object and these methods is found below.

Function Main()
     ...
     Set oFS = CreateObject("Scripting.FileSystemObject")
     DTSPackageLog.WriteStringToLog "Opening File"
     Set oTS = oFS.OpenTextFile("C:\FileNotFound.TXT", 1)     
     If (Err.Number <> 0) OR (oTS Is Nothing) Then
           DTSPackageLog.WriteTaskRecord Err.Number, Err.Description
           Main = DTSTaskExecResult_Failure
     End If
          
End Function

This example illustrates how the developer of the BI solution is allowed to implement a rich environment for logging information and errors to the DTS logging destinations.

Practice Summary

Benefits

Managing Auditing and Error Handling from within DTS offers many options for implementation depending on the level of functionality and coverage desired.

Among some of the main benefits of implementing auditing and error handling include:

  • Data validation capabilities using auditing checks and balances
  • More precise control over error handling and how DTS responds to errors
  • Rich information captured during logging to make error resolution easier
  • Using and capturing data lineage information to each piece of data can be tracked back to the source
  • DTS can log events and errors to locations that make error management possible by external applications (SQLAgent, Event Log, and so forth)

Precautions

The main precaution area to remember is that while using external components to handle auditing and logging, a level of complexity is added to the overall solution. Remember to document thoroughly any components and code so that other developers can follow and resolve issues.

Enhancing DTS Functionality Practices

Developing Custom Tasks with Visual Basic .NET Approach

Extensibility with Custom Task Support

While many would argue that DTS, as a product, provides for a great deal of flexible, off-the-shelf functionality, in the development of BI solutions with DTS, there are occurrences when this functionality needs to be extended. One method for extending DTS is through the development of Custom Tasks. Custom Tasks provide the ability to encapsulate core logic into a single DLL consisting of properties and execution methods. This logic can then be registered and used within the DTS package environment, similar to any other task. In fact, the Analysis Services Processing Task described earlier, is a good example of a custom task added to the DTS design environment for an extended function.

Required DTS COM Interfaces

CustomTask Interface

The manner in which DTS supports this type of extensibility is by requiring the custom task developer to implement the DTS.CustomTask COM interface, found within the Data Transformation Services Package Object model. This interface provides the principal Execute() method that the Package environment can invoke at runtime, to include the task within the workflow of the package's execution. A graphical representation of this interface is shown below.

Aa902667.sql_busintbpwithdts_16(en-us,SQL.80).gif

Figure 16. CustomTask COM interface

The primary modifications made within the implementation of the CustomTask interface are placing most core logic within the Execute() method and adding extended, developer defined properties to the task. The properties are often needed to achieve the additional logic.

CustomTaskUI Interface

To deliver a customized user interface for the task's property pages requires an implementation of the DTS.CustomTaskUI COM interface. Doing so, allows the developer to create rich user interfaces to support the definition of properties for the custom task.

PersistPropertyBag Interface

The final interface to be discussed in delivering the Visual Basic .NET custom task is the DTS.PersistPropertyBag COM interface. The PersistPropertyBag interface provides an interface that DTS will use to load the task's properties from and save the task's properties within the underlying package structure, as appropriate.

Note   At the time of this writing, for the Visual Basic .NET implementation of a custom task, all three of these interfaces are required to deliver a comprehensive custom task to integrate within the DTS portion of the BI Solution.

Visual Basic .NET and Component Object Mode (COM) Interoperability

A key element to understanding the nuances of developing the Visual Basic .NET custom task is the interoperability layer between COM and the Common Language Runtime of the .NET Framework. In order for the Visual Basic .NET class to implement the prescribed DTS COM interface, this interoperability (or interop) layer must be defined through the creation of a Runtime Callable Wrapper (or RCW) for Visual Basic .NET to utilize the COM interface as a .NET assembly. The opposite is true for DTS, a collection of COM servers, to use the Custom Task's Visual Basic .NET assembly. A COM Callable Wrapper (or CCW) must be established for DTS to interact with the Visual Basic .NET assembly. The graphic below provides an illustration of the interoperability between the two platforms.

Aa902667.sql_busintbpwithdts_17(en-us,SQL.80).gif

Figure 17. COM and Runtime Callable Wrappers

Essential .NET Utilities

The following Visual Studio .NET utilities will be used to create the aforementioned wrappers and register the resulting DLL, in .NET referred to as the Assembly, within the .NET Global Assembly Cache.

Type Library importer (TLBIMP.exe) – provides the ability to import type libraries (descriptors of COM Interfaces) and create a runtime callable wrapper (RCW). The runtime callable wrapper is output as a DLL that can be registered within the Visual Basic .NET project.

Strong name tool (SN.exe) – provides a strong name key (.SNK) file to be used within the project's assembly

Assembly registration tool (REGASM.exe) – provides the ability to register the .NET assembly within the registry so that a COM component could utilize the interfaces provided by the assembly.

Global Assembly Cache tool (GACUTIL.exe) – provides a registration of the wrappers into the Global Assembly Cache for the final step in interoperability between DTS and the Visual Basic .NET custom task.

Each of these tools has its place in the process of coding the Visual Basic .NET custom task.

Coding the Visual Basic .NET Custom Task

The custom task example being developed for this best practice is an Error Handler custom task. The behavior of this task, shown in the package illustration below, is to retrieve the error information from the preceding step, defined by an OnFailure workflow constraint, and log it to a textual log file, the Windows event log, or both.

Aa902667.sql_busintbpwithdts_18(en-us,SQL.80).gif

Figure 18. Error Handler custom task package

In order to best explain the approach of building the Visual Basic .NET custom task, all the relevant concepts and a portion of supporting code samples for developing the custom task code will be covered in this section. A full listing of the entire .NET error handler custom task code can be found in Appendix B.

Getting Started

Following the initial creation of the Visual Basic .NET class project, one of the first tasks is setting up the interoperability with the .NET managed code and the COM objects used for the custom task. The first step is to create the Strong Name key file (.SNK) to be included within the assembly. Assuming the custom task code was in a directory, C:\dotNetErrorHandler\, the following command line should be run:

 "<your VS.NET Folder>\FrameworkSDK\Bin\sn.exe" -k 
      "C:\dotNetErrorHandler\dotNetErrorHandler.snk"

Following the creation of the strong name key file, a key file attribute should be added within the assembly's code itself, found in AssemblyInfo.vb. The one line of code to add looks like the following:

'Note for the CustomTask to work you must generate and reference a KeyFile
<Assembly: AssemblyKeyFile(".\dotNetErrorHandler.snk")> 

The next step is to create the Runtime Callable Wrapper (RCW) and include a reference to it within the project. The command to create the RCW with the type library importer is as follows:

"<VS .NET Folder>\FrameworkSDK\Bin\tlbimp.exe" "C:\Program Files\Microsoft 
   SQL Server\80\Tools\Binn\dtspkg.dll" 
      /keyfile:"C:\dotNetErrorHandler\dotNetErrorHandler.snk" /out: 
         "C:\dotNetErrorHandler\Interop.DTS.dll"

This command constructs the RCW, specifically Interop.DTS.dll, for the DTS Package Object COM Library to be used in this project.

Once these preliminary steps have been accomplished, the reference to the RCW can be established, as shown below:

Click here to see larger image

Figure 19. Establishing the reference to the RCW (click image to see larger picture)

Implementing the Right Interfaces

Earlier in this best practices section, it was noted that the custom task class must implement three interfaces. This class, named EHTask, begins with the implements declaration.

Public Class EHTask 
    'You must implement the CustomTask, 
    'CustomTaskUI and PersistPropertyBag interfaces
    Implements Interop.DTS.CustomTask
    Implements Interop.DTS.CustomTaskUI
    Implements Interop.DTS.PersistPropertyBag

These three implements statements set the basis for the task development, including the implementation of all required properties and methods of each interface. Before continuing with that step, it is a good idea to create private variables to hold the values of the public properties of the task. Here is the variable declaration section for the EHTask class.

    'Create private variables to hold the standard properties
    Private _Name As String
    Private _Description As String

    'Create a private reference to the task object
    Private _TaskObject As Interop.DTS.Task

    'Create other private variables to store the extended properties
    Private _LogFileName As String
    Private _LogToEventLog As Boolean
    Private _LogToFile As Boolean
    Private _HandledStepName As String
    Private _UseGlobals As Boolean

    'Friend variable for the UI to report whether or not it was cancelled    
    Friend bolUICancelled As Boolean

Now the task of implementing interfaces is at hand. The first interface to evaluate is the DTS.CustomTask interface.

DTS.CustomTask Interface

The DTS.CustomTask Interface is an implementation of standard properties (Name and Description), execution method and a properties provider for serving requests for the properties collection of the custom task object. The first area of focus is the code to support the standard properties.

Public Property Description() As String _
   Implements Interop.DTS.CustomTask.Description
        Get
            Return _Description

        End Get
        Set(ByVal Value As String)
            _Description = Value
        End Set
End Property

Public Property Name() As String Implements Interop.DTS.CustomTask.Name
        Get
            Return _Name
        End Get
        Set(ByVal Value As String)
            _Name = Value
        End Set
End Property

Additional properties required for the .NET Error Handler custom task are:

  • HandledStepName – the internal name for the step, which the custom task will interrogate through the GetExecutionErrorInfo() method to determine error information.
  • LogToFile - a Boolean indicating whether or not to log the captured error information to a file
  • LogFileName – the name and full path to the log file where the information should be stored
  • LogToEventLog – a Boolean indicating whether or not to log the captured error information to the Windows Application event log.
  • UseGlobals – a Boolean indicating whether or not the values for the LogToFile, LogFileName, and LogToEventLog properties should be pulled from global variables within the package. Given that there will likely be more than one error handler in the DTS package, this property makes certain that all .NET Error Handler tasks use the same settings.

Following the standard properties, Description and Name—and the properties domain to the individual custom task—is the standard Execute() method. Below is the entire code listing for the method, followed by explanations of the flow and subroutines called within it.

Public Sub Execute(ByVal pPackage As Object, _
                   ByVal pPackageEvents As Object, _
                   ByVal pPackageLog As Object, _
                   ByRef pTaskResult As Interop.DTS.DTSTaskExecResult) _ 
                          Implements Interop.DTS.CustomTask.Execute

        Dim objPackage As Interop.DTS.Package
        Dim objTasks As Interop.DTS.Tasks
        Dim objStep As Interop.DTS.Step
        Dim objPackageLog As Interop.DTS.PackageLog

        Dim strSource As String
        Dim lngNumber As Long
        Dim strDescription As String
        Dim strErrorString As String

 Try
            'Cast the passed params into the 
            'local stronger typed variables
            objPackage = pPackage
            objPackageLog = pPackageLog

            'Make sure we have a precedence step
            Call EstablishPrecedence(objPackage)

            'Check the HandledStepName and Throw an Exception
            If Me.HandledStepName = "" Then
                Throw New System.Exception( "Task cannot execute due to 
                    no Precedence Constraint being defined.")
            End If

            'If the Task is defined to drive from 
            'the global variables we need to check
            Call ReadFromGlobalVariables(objPackage)

            'Get a Step Object Reference
            objStep = objPackage.Steps.Item(Me.HandledStepName)

            'Retrieve the Error Information
            Call objStep.GetExecutionErrorInfo(lngNumber, strSource, _
                                               strDescription)

       'Build the Pipe delimited Error String
       strErrorString = Format(Now, "MM/dd/yyyy hh:mm:ss tt") & "|" & _
           objPackage.Name & "|" & objStep.Name & "|" & _
           " The Step, " & objStep.Description & _
           " failed with the following error: " & _
           lngNumber.ToString() & " - " & strSource & " - " &   
           strDescription

            'Determine if you should write the error to a log file
            If Me.LogToFile And Trim(Me.LogFileName) = "" Then
                'Throw an exception due to the lack of a filename
                Throw New System.Exception("Task cannot log to file due to 
                    no log file being defined.")
            ElseIf Me.LogToFile And Trim(Me.LogFileName) <> "" Then
                'Write to the log file
                Dim objFile As System.IO.File
                Dim objStreamWriter As System.IO.StreamWriter
                objStreamWriter = objFile.AppendText(Me.LogFileName)

                'Close the StreamWriter and Clean up the fileobject
                objStreamWriter.WriteLine(strErrorString)
                objStreamWriter.Close()
                objStreamWriter = Nothing
                objFile = Nothing

            End If

            'Determine if you should write to the eventlog
            If Me.LogToEventLog Then
                Dim objEventLog As New EventLog()
                objEventLog.WriteEntry(".Net DTS Error Handler", _
                       strErrorString)
                objEventLog = Nothing
            End If

            'Return Success as this Execution was successful
     pTaskResult = Interop.DTS.DTSTaskExecResult.DTSTaskExecResult_Success

 Catch ex As Exception
           Throw New System.Exception("An Error occured during the tasks 
               execution.", ex)

            'Return Failure as this Execution was a failure
     pTaskResult = Interop.DTS.DTSTaskExecResult.DTSTaskExecResult_Failure

 End Try

 End Sub

The Execute method handled the processing in the following order:

  • Determining if a preceding step existed through the EstablishPrecedence method
  • Evaluating and populating the rest of the task's properties from global variables based upon the UseGlobals setting within the subroutine, ReadFromGlobalVariables()
  • Capturing the error information for the Step object, defined by name in the HandledStepName property
  • Writing the information to either or both the LogFile destination or the Event Log destination

The only other method to be implemented is the Properties property method for accessing the properties collection of the custom task.

In the .NET Error Handler example, the properties collection is populated via the implementation of a PropertiesProvider. This provider fills a properties collection through the GetPropertiesForObject method call.

Public ReadOnly Property Properties() As Interop.DTS.Properties _
            Implements Interop.DTS.CustomTask.Properties
    Get
      'Declare variables for Properties collection 
      'and ProviderClass to retrieve property info
      Dim objProperties As Interop.DTS.Properties
      Dim objPropProvider As _
               Interop.DTS.PropertiesProviderClass = _
               New Interop.DTS.PropertiesProviderClass()

      'Using the reference to the existing 
      'task object, get a reference to the task object 
      'to pass to the Properties Provider    
       objProperties = objPropProvider.GetPropertiesForObject(_TaskObject)


      'Perform some cleanup on the properties provider    
      objPropProvider = Nothing


      'Return the value of the properties collection    
      Return objProperties

    End Get
End Property
The next interface, required for a custom task written with VB .NET 
   is the CustomTaskUI interface.

DTS.CustomTaskUI Interface

The DTS.CustomTaskUI Interface is an implementation of the standard methods for serving requests made specifically by the DTS Designer. In order to successfully implement this interface for the .NET Error Handler example, all methods must be stubbed in but only the Initialize, Edit, New (as an aliased method) and Delete contain actual logic. These populated methods are listed below and supporting commentary is offered as needed.

Public Sub Initialize(ByVal pTask As Interop.DTS.Task) _
    Implements Interop.DTS.CustomTaskUI.Initialize
        _TaskObject = pTask
End Sub

The Initialize method provides very important information to the custom task in the form of the pTask parameter variable shown above. The pTask parameter provides the custom task with the identity of itself as an object for future references within the DTS package object hierarchy. The next method is the Edit method, which is called by the DTS designer whenever the developer takes an action to launch the task's Properties dialog.

Public Sub Edit(ByVal hwndParent As Integer) _ 
     Implements Interop.DTS.CustomTaskUI.Edit
        'Should Show a UI Here
        ShowPropertyUI()
End Sub

Public Sub Delete(ByVal hwndParent As Integer) _ 
     Implements Interop.DTS.CustomTaskUI.Delete

        _TaskObject = Nothing
End Sub

The Delete method provides the task with the opportunity to clean up any internal references, such as the _TaskObject reference captured originally in the Initialize method. In the example above, the _TaskObject reference is cleared and the discarded object is relegated to the Common Language Runtime Garbage Collector.

Due to language restrictions, the implementation of the New method must be aliased. In the case of the example shown below, New2 was used as the alias for the New method.

    Public Sub New2(ByVal hwndParent As Integer) _
              Implements Interop.DTS.CustomTaskUI.New

        'Preset the Settings for the task
        Me.LogToEventLog = True
        Me.LogToFile = True
        Me.LogFileName = ""
        Me.UseGlobals = True

        'Should Show a UI Here
        ShowPropertyUI()


    End Sub

Within the New method, properties of the task are preset and the user interface is presented to the user for the first time. Below are the UI elements of the custom task.

Aa902667.sql_busintbpwithdts_20(en-us,SQL.80).gif

Figure 20. .NET Error Handler

The user is presented with the above dialog every time the property page for the task is evoked and no preceding step is found.

Aa902667.sql_busintbpwithdts_21(en-us,SQL.80).gif

Figure 21. NET Error Handler Task Properties

Here the properties being stored are presented in the illustration above. As a precedent step is included, the description for the task will change. It is also true that if the global variables, which are created by the first instance of this task, are modified and the Use Global Variable option is checked, other properties may change.

Aa902667.sql_busintbpwithdts_22(en-us,SQL.80).gif

Figure 22. Notification of updated global variables

The dialog above notifies the user anytime that the global variables are updated upon closing the Properties dialog for the custom task.

DTS.PersistPropertyBag Interface

The DTS.PersistPropertyBag interface is an essential interface to the properties stored with the package. This is solely the case when dealing with properties that are not being exposed by the DTS.CustomTask properties collection. This is true with the .NET Error Handler custom task. Through the implementation of the PersistPropertyBag interface, however, all properties that are defined for the custom task are capable of being saved and loaded. The two methods required for the PersistPropertyBag interfaces implementation are listed below.

    Public Sub Load(ByVal PropertyBag As Interop.DTS.PropertyBag) _ 
             Implements Interop.DTS.PersistPropertyBag.Load
        'Create all of the PropertyBag readers to read information                     
        'from the PropertyBag into the class level variables

        _Name = PropertyBag.Read("Name")
        _Description = PropertyBag.Read("Description")
        _LogFileName = PropertyBag.Read("LogFileName")
        _LogToEventLog = PropertyBag.Read("LogToEventLog")
        _LogToFile = PropertyBag.Read("LogToFile")
        _HandledStepName = PropertyBag.Read("HandledStepName")
        _UseGlobals = PropertyBag.Read("UseGlobals")

    End Sub

    Public Sub Save(ByVal PropertyBag As Interop.DTS.PropertyBag) _ 
             Implements Interop.DTS.PersistPropertyBag.Save
        'Create all of the PropertyBag writers to persist information         
        'from the class level variables into the property bag

        PropertyBag.Write("Name", _Name)
        PropertyBag.Write("Description", _Description)
        PropertyBag.Write("LogFileName", _LogFileName)
        PropertyBag.Write("LogToEventLog", _LogToEventLog)
        PropertyBag.Write("LogToFile", _LogToFile)
        PropertyBag.Write("HandledStepName", _HandledStepName)
        PropertyBag.Write("UseGlobals", _UseGlobals)

    End Sub

Registering the Visual Basic .NET Custom Task

Unfortunately as of this writing, there is no way using the DTS design environment user interface to register a Visual Basic .NET custom task. The following approach includes editing the registry, which is a dramatic but necessary step to making a Visual Basic .NET custom task available in DTS.

  1. Develop the custom task using Visual Basic .NET as prescribed in the prior section and Appendix B.
  2. Build the assembly for the .NET custom task solution.
  3. Register the Assembly, using the Assembly Registration Tool, for the resulting .NET DLL (somewhat similar to Regsvr32.exe).

    For example:

    C:\WINDOWS\Microsoft.NET\Framework\v1.0.3705\RegAsm.exe  
         "C:\dotNetErrorHandler\bin\dotNetErrorHandler.dll"
    
  4. Register the assembly using the Assembly Registration Tool, for the Runtime Callable Wrapper, which provides the .NET and COM interoperability, for the DTS Package Object library.

    For example:

    C:\WINDOWS\Microsoft.NET\Framework\v1.0.3705\RegAsm.exe  
         "C:\dotNetErrorHandler\Interop.DTS.dll"
    
  5. Add the registered assemblies from Steps 3 and 4 to the .NET Global Assembly Cache (GAC), using the Global Assembly Cache tool.

    For example:

    "C:\Program Files\Microsoft Visual Studio .NET\FrameworkSDK\Bin\gacutil.exe" 
         /i "C:\ dotNetErrorHandler \bin\ dotNetErrorHandler.dll"
    and then
    "C:\Program Files\Microsoft Visual Studio .NET\FrameworkSDK\Bin\gacutil.exe" 
         /i "C:\ dotNetErrorHandler \Interop.DTS.dll"
    
    Note   Each time the solution is re-built; the first command placing the .NET assembly into the GAC will have to be run.
  6. Now the registry will have to be edited, as there is no present way to register a .NET custom task via the DTS user interface. It is worth noting that users who are not familiar with editing the registry should not attempt this as it can have less than desirable results.

    The following items will need to be known before editing the registry:

    • Full Path to the DLL (i.e., C:\dotNetErrorHandler\bin\dotNetErrorHandler.dll)
    • Full Path to a DLL or .ICO file with an icon as the resource (optional)
    • Description of the task (kept short – i.e., .NET Error Handler)
    • The GUID for the DLL – The GUID can be found for the assembly in the AssemblyInfo.vb file under the entry:
      <Assembly: Guid("GUID DEFINED HERE")> 
      
    • ProgID for the task – (i.e., the way this object would be called from CreateObject under COM, for example, "dotNetErrorHandler.EHTask")

    Once all of the above items have been established, a registry entries file is ready to be created to update the registry. The template registry entries file for this approach is:

    Windows Registry Editor Version 5.00
    HKEY_CLASSES_ROOT\CLSID\{<The GUID>}]
    @="<The Prog ID>"
    "AppID"="{<The GUID>}"
    "DTSIconFile"="<Path to the DLL>"
    "DTSIconIndex"=dword:00000000
    "DTSTaskDescription"="<The Task Description>"
    
    [HKEY_CLASSES_ROOT\CLSID\{<The GUID>}\Implemented Categories]
    
    [HKEY_CLASSES_ROOT\CLSID\{<The GUID>}\Implemented Categories\
         {10020200-EB1C-11CF-AE6E-00AA004A34D5}]
    
    [HKEY_CLASSES_ROOT\CLSID\{<The GUID>}\Implemented Categories\
         {40FC6ED5-2438-11CF-A3DB-080036F12502}]
    
    [HKEY_CLASSES_ROOT\CLSID\{<The GUID>}\InprocServer32]
    @="<Path to the DLL>"
    "ThreadingModel"="Both"
    
    [HKEY_CLASSES_ROOT\CLSID\{<The GUID>}\ProgID]
    @="<The PROGID>"
    
    [HKEY_CLASSES_ROOT\CLSID\{<The GUID>}\Programmable]
    
    [HKEY_CURRENT_USER\Software\Microsoft\Microsoft SQL Server
         \80\DTS\Enumeration\Tasks\<The GUID>]
    @="<The PROGID>"
    

    Below are the contents of the actual registry entries file for the example custom task. These contents can be copied to text editor and saved to TaskReg.reg or another suitable .reg filename.

    Windows Registry Editor Version 5.00
    
    [HKEY_CLASSES_ROOT\CLSID\{EF4AC3E4-3D72-4117-AB91-8B417826792F}]
    @="dotNetErrorHandler.EHTask"
    "AppID"="{EF4AC3E4-3D72-4117-AB91-8B417826792F}"
    "DTSIconFile"="C:\\dotNetErrorHandler\\ErrorHandler.ICO"
    "DTSIconIndex"=dword:00000000
    "DTSTaskDescription"=".NET Error Handler"
    
    [HKEY_CLASSES_ROOT\CLSID\{EF4AC3E4-3D72-4117-AB91-8B417826792F}
         \Implemented Categories]
    
    [HKEY_CLASSES_ROOT\CLSID\{EF4AC3E4-3D72-4117-AB91-8B417826792F}
         \Implemented Categories\{10020200-EB1C-11CF-AE6E-00AA004A34D5}]
    
    [HKEY_CLASSES_ROOT\CLSID\{EF4AC3E4-3D72-4117-AB91-8B417826792F}
         \Implemented Categories\{40FC6ED5-2438-11CF-A3DB-080036F12502}]
    
    [HKEY_CLASSES_ROOT\CLSID\{EF4AC3E4-3D72-4117-AB91-8B417826792F}
         \InprocServer32]
    @="C:\dotNetErrorHandler\bin\dotNetErrorHandler.dll"
    "ThreadingModel"="Both"
    
    [HKEY_CLASSES_ROOT\CLSID\{EF4AC3E4-3D72-4117-AB91-8B417826792F}\ProgID]
    @="dotNetErrorHandler.EHTask"
    
    [HKEY_CLASSES_ROOT\CLSID\{EF4AC3E4-3D72-4117-AB91-8B417826792F}
         \Programmable]
    
    [HKEY_CURRENT_USER\Software\Microsoft\Microsoft SQL Server
         \80\DTS\Enumeration\Tasks\EF4AC3E4-3D72-4117-AB91-8B417826792F]
    @="dotNetErrorHandler.EHTask"
    
  7. Open the DTS Designer. The custom task should now be visible as a registered entry in the Task Toolbar.

Guidelines for Developing and Deploying the Custom Task

The approach to developing a custom task in Visual Basic .NET is not altogether different than the approach within Visual Basic 6.0. Regardless of the experience level with custom tasks and prior languages, here is a collection of guidelines to successfully achieve the Visual Basic .NET Error Handler custom task.

  • Take time to understand the benefits of going down the Visual Basic .NET path. In terms of best practice, using Visual Basic .NET gives the developer the ability to create custom tasks that do not need to execute on the main thread as the Visual Basic .NET solution is free threaded and does not suffer from thread affinity.
  • In terms of developing the custom task, start slow. Create a base shell for the custom task, compile and register it with DTS using the approaches described here. Once the foundation is in place, developing and deploying updated features is the next incremental step.
  • Consider building the task using Visual Basic 6.0. Why? One, it is a good language that allows for the eased development of a custom task. Two, it provides a starting point for learning the differences between COM and .NET. Finally, doing so determines whether or not .NET needs to be used due to threading implications. If the task can be run on the main thread with no issues, there will be less of a need to make migrating, to a .NET custom task, a priority.
  • As with new languages and frameworks, there can sometimes be elements that need work. In the .NET Error Handler example, the task implemented the properties provider within the DTS.CustomTaskUI.Properties() method. The main reason for this was a quirk with the example not correctly allowing the DTS Design Environment to manage the properties. As of this writing, there is no known way to resolve this dilemma.
  • By all means, expect and look for ways to achieve the same functionality with less code. The .NET Framework base classes are exceptionally rich and provide for a superior way of achieving Win32 functionality than solely using COM and the Win32 API (notice it took only three lines of .NET code to add Windows event logging to the custom task!).
  • Learn more about deploying a .NET custom task by exploring and saving a copy of the registry nodes that may be affected by the deployment. The existing tasks within the registry can certainly portray the necessary elements for being successful in registering a .NET custom task for use in DTS.

Following these guidelines will build a better foundation for developing a Visual Basic .NET custom task and making the task a successful element of the overall BI Solution.

Making ActiveX Script Reusable

Local Context of Script

There are many places within DTS where ActiveX Script can be used to augment the existing tasks, and even custom tasks within the package. Whether it is an ActiveX Script workflow, an ActiveX Script task, or an ActiveX Script transform, each of these unique elements has one apparently limiting characteristic in common. The script within each of these environments cannot be used from another task and the only way ActiveX scripts have of communicating between tasks is through global variables. The execution of script within a local context to each task, workflow, or transform creates a costly method for maintaining common scripting functions that are repeated throughout a DTS package infrastructure or a BI solution.

Entry Functions and Parsed Script Executions

While the Entry Function property for ActiveX Scripting is indeed the function the ActiveX task, workflow or transformation called when one of these components is executed, this function is not necessarily the first ActiveX Script to be executed. Any script defined in the "global" area of the script will be executed prior to the entry function call. Below is an example of a simple ActiveX Script illustrating this execution order.

Aa902667.sql_busintbpwithdts_23(en-us,SQL.80).gif

Figure 23. ActiveX Script illustrating this execution order

The above script consists of two msgbox method calls: one prior to the Main() entry function and one within the function. The results are two message box dialogs in order.

Aa902667.sql_busintbpwithdts_24(en-us,SQL.80).gif

Figure 24. Message box dialogs from the script execution

It is also important to note that the entry function does not have to be Main and as the approach to building re-usable ActiveX Script is explored, a strong case will be made for using alternate entry function names, too.

ActiveXScript Property

One of the first steps to making ActiveX Script available is to understand that each task, Workflow or Transform has an ActiveXScript property, which is accessible throughout the package object hierarchy. This property provides a string populated with all of the accessed object's ActiveX script.

Having the script of another task in a string is the beginning phase of reusing ActiveXScript.

Execute() and eval()

The VBScript and JScript ActiveX scripting languages are used most commonly within DTS scripting. Each of these languages provides for a runtime means of including script defined within a string.

In VBScript, this support is provided by the Execute() function. The Execute() function takes a string consisting of VBScript commands as a single parameter. The commands and functions defined within the string are included in the runtime environment and can be called like any other command. Below is an example of this concept.

Aa902667.sql_busintbpwithdts_25(en-us,SQL.80).gif

Figure 25. Runtime that includes script defined within a string

This example builds on the previous example and three message box dialogs are displayed. One is the result of the first msgbox statement within the script, the second is the result of the msgbox() call within the Main() function and the third is the result of a call to the dynamically added NewMessage() function, included through use of the Execute() function within VBScript.

JScript allows for an entirely similar approach using the eval() function. All of the behavior is the same and the only differences are in the language semantics. The same functionality can be achieved.

Developing a Single Scripting Code Base

For the developer using scripting, and common script functions, on a regular basis, the best re-use probably achieved has been through copy and paste between DTS tasks or between DTS tasks and packages. Once this initial copy and paste is performed, the code has been shared between packages and potentially will branch, that is, allow for changes which are not propagated throughout every occurrence of the function in all packages and tasks.

These methods can be overcome partially through the modular development of Parent/Child packages and further eliminated through the development of custom tasks. However, neither of these approaches can eliminate this situation entirely. The real solution is to develop a single code base that can be included in packages at runtime and referenced by the package's ActiveX Script components.

The Single Code Base Package

Below is an illustration of the example package for developing a single scripting code base.

Aa902667.sql_busintbpwithdts_26(en-us,SQL.80).gif

Figure 26. Single Code Base Package example

Each element will be described in further detail starting with the "Load Script File" ActiveX Script task.

Load Script File – leveraging the File System Object

The FileSystemObject is one of the most flexible elements of the scripting runtime as it provides necessary access to the file system from within ActiveX script. Our approach and example will start with code that uses the FileSystemObject to load a common Visual Basic script for OLAP Partition management from an ASCII text file, store it within an ActiveX Script task called the "Code Library" and allow for the continued execution of the package.

Here is the ActiveX Script for the task:

Function Main()
'This function is responsible for loading 
'the script file defined in the global variable - gstrScriptFile

Dim objFSO 
Dim objFile
Dim objPackage
Dim objTask
Dim strScript

'Read the common olap partition processing script
set objFSO = CreateObject("Scripting.FileSystemObject")
set objFile = objFSO..OpenTextFile
     (DTSGlobalVariables("gstrScriptFile").Value, forReading)
strScript = objFile.ReadAll
objFile.Close

'Place the common script into the task,  "Code library"
set objPackage = DTSGlobalVariables.Parent
set objTask = objPackage.Tasks(cstr("DTSTask_DTSActiveScriptTask_3"))

objTask.CustomTask.ActiveXScript.Value = strScript

set objTask = nothing
set objPackage = nothing
set objFile = nothing
set objFSO = nothing

Main = DTSTaskExecResult_Success
End Function

Following the execution of the above script, the Code Library task should contain a copy of the data within the text file. Before continuing on the workflow to the Create Partition task, it is important to understand the choice for and configuration of the Code Library task.

Code Library Task

The Code Library ActiveX Script task acts as the runtime repository for the script loaded from the text file. The reason for choosing the Code Library task approach is simply:

  • The ActiveX Script task can hold a great deal of scripting text supporting the approach of loading large common scripting files.
  • The ActiveX Script task supports viewing of the script that has been loaded.
  • The ActiveX Script task supports parsing the script file(s), which has been loaded, allowing for easier debugging.

In order to use the ActiveX Script task for this purpose, it is advisable to change the entry function to something other than Main(). This point will be clearer in the next task. Secondly, the step's workflow property for Disable this Step should be set to true as is shown below.

Aa902667.sql_busintbpwithdts_27(en-us,SQL.80).gif

Figure 27. Setting the "Disable this Step" property to true

This latter step makes certain that the task will not be executed as it lives outside of the workflow of the other tasks.

Create Partition – Reusing Standardized Scripting

The Create Partition ActiveX Script task really begins to illustrate the power of re-usable code by showing a dramatic reduction in overall code within the task for a less than simple function. Through reference of the Code Library task and use of the Execute() function within VBScript, the contents of the Code Library tasks are loaded dynamically into the runtime environment of the Create Partition task. Thus, allowing the task to reference functions that are defined in the text file loaded previously.

Here is the code listing for the task:

Dim strCommonScript
Dim objPackage
Dim objTask
Set objPackage = DTSGlobalVariables.Parent
Set objTask = objPackage.Tasks("DTSStep_DTSActiveScriptTask_3")

strCommonScript = objTask.CustomTask.ActiveXScript.Value
Execute(strCommonScript)

Function Main()
Dim strTreeKey 'The hierarchy of the OLAP Object to Process

'Run the Dynamically added function which gathers the parameters for 
'global variables and builds a TreeKey for use in 
'the Analysis Services Processing Task!   
call CreatePartition(strTreeKey)

'Place the TreeKey in the Global Variable
DTSGlobalVariables("TreeKey").Value = strTreeKey

Main = DTSTaskExecResult_Success
End Function

The CreatePartition() logic is similar to the logic shown earlier in the white paper. This logic consists of a great deal more code than the three lines of code it took here, within the Main function, to perform the partition creation. By naming the entry function in the Code Library task by a name other than Main(), there were no conflicts. However, had we tried to use the same entry function name after reading the ActiveX Script from the Code Library task, conflicts would have existed prohibiting this reuse approach.

Guidelines

Delivering a reusable standardized scripting code base is actually a much easier task than some of the other approaches a person might take, to leverage the intellectual property built up over time for doing scripting within DTS. In order to gain value from the investments already made within the DTS architecture, there are a few guidelines to keep in mind.

  • Consider storing the ActiveX script in a text file or in multiple files. This promotes the control of the scripting outside of the DTS environment and allows for the use of products like Visual Source Safe to manage the versioning of the common script.
  • Load the text file using the File System Object in lieu of other methods to avoid issues like contention and File Locking.
  • Strive for the utmost modularity. A reusable scripting function should pass parameters, definitely, or rely upon settings within global variables, if absolutely necessary. It is important to not build dependencies between a variable within the task and use of that variable within the dynamic script.
  • Sometimes modularity can be better achieved in multiple DTS packages. Keep in mind that SQL Server 2000 provides even greater support for the nested child packages and the use of child packages may be a more prudent solution for achieving reusability.

Practice Summary

Benefits

Enhancing the functionality of DTS results in an even more robust component of the SQL Server BI platform. Some of the core benefits realized by these enhancement practices are:

  • Modularity

    Through implementation of the approaches shown, a more modular approach will be enforced in the development of DTS packages resulting in other intrinsic benefits.

  • Standardization

    Employing these approaches will result in greater overall standardization of the DTS package architecture. Through standardization greater efficiencies can and will be achieved in the overall development, maintenance and execution of the DTS packages within BI solutions.

  • Limitless Functionality

    Through incorporation of the technical approaches discussed, many areas of other functionality can be introduced through additional COM or .NET components. Integrating these components can and often will far exceed the standard capabilities of traditional BI ETL platforms.

Precautions

A limited number of precautionary areas do exist for this practice. The main precaution to keep in mind is complexity.

Complexity

Complexity chiefly results from mismanaged implementations of logic and taking a more reactive approach to development than a proactive one. Using these techniques require the core investment of time in the design of the solution, to establish the foundation for common logic built into reusable script and custom tasks.

Conclusion

DTS is a flexible architecture providing the opportunity to build comprehensive solutions within the Microsoft Data Warehousing Framework. This white paper has highlighted the prominent best practices for using DTS within BI solutions. Practical exploration of the best practices and approaches discussed will offer an opportunity to enhance and tailor these techniques for the individual solution architecture.

For More Information

SQL Server 2000 Books Online contains more information about the Microsoft Data Warehousing Framework, the SQL Server relational database, Data Transformation Services, and Analysis Services. For additional information, see the following resources:

Appendix A – About the Authors

Trey Johnson, member of the Board of Directors for the Professional Association for SQL Server (PASS), is a Business Intelligence Architect with Encore Development, an innovative software consulting firm specializing in Web-powered business solutions. Johnson's responsibilities include supporting Encore's delivery of complete Business Intelligence solutions to mid-market and Fortune 1000 organizations. Johnson has been involved with Decision Support, Data Warehousing, and Data Mining on SQL Server since 1994. Johnson's industry experiences include speaking at SQL Server industry conferences, including multiple PASS Conferences, multiple VSLive! SQL2TheMAX conferences and EDevCon 2000, application of Artificial Intelligence Data Cleansing/Mining, Relational Decision Support and Data Warehousing/OLAP to health care, financial, retail, industrial warehousing and law enforcement organizations.

Mark Chaffin is the Practice Director for Business Intelligence with Encore Development, an innovative software consulting firm specializing in Web-powered business solutions. He has been the primary architect of many business intelligence solutions for clients in many vertical markets including retail, consumer packaged goods, healthcare, finance, marketing, banking, technology, and sports and entertainment. He has experience in click-stream analytics, data mining, transactional application architecture, Internet application architecture, database administration, and database design. He is also the co-author of SQL Server 2000 Data Transformation Services from Wrox Press, and has authored many articles on business intelligence, SQL Server, DTS and Analysis Services.

Appendix B – Code Listing for .NET Custom Task

Below is all of the code for the Visual Basic .NET error handler task by individual file name.

AssemblyInfo.VB File Contents

Imports System.Reflection
Imports System.Runtime.InteropServices

' General Information about an assembly is controlled through the following 
' set of attributes. Change these attribute values to modify the information
' associated with an assembly.

' Review the values of the assembly attributes

<Assembly: AssemblyTitle(".NET Error Handler")> 
<Assembly: AssemblyDescription("DTS Custom Task handling errors originating 
     in other Steps")> 
<Assembly: AssemblyCompany("Encore Development")> 
<Assembly: AssemblyProduct(".NET Error Handler")> 
<Assembly: AssemblyCopyright("")> 
<Assembly: AssemblyTrademark("")> 
<Assembly: CLSCompliant(True)> 

'The following GUID is for the ID of the typelib if this 
     project is exposed to COM
<Assembly: Guid("EF4AC3E4-3D72-4117-AB91-8B417826792F")> 

' Version information for an assembly consists of the following four values:
'
'      Major Version
'      Minor Version 
'      Build Number
'      Revision
'
' You can specify all the values or you can default the Build and 
     Revision Numbers 
' by using the '*' as shown below:

<Assembly: AssemblyVersion("1.0.*")> 

'Note for the CustomTask to work you must generate and reference a KeyFile
<Assembly: AssemblyKeyFile(".\dotNetErrorHandler.snk")>

EHTask.VB File Contents

Public Class EHTask 
    'You must implement the CustomTask, 
    'CustomTaskUI and PersistPropertyBag interfaces
    Implements Interop.DTS.CustomTask
    Implements Interop.DTS.CustomTaskUI
    Implements Interop.DTS.PersistPropertyBag

    'Create private variables to hold the standard properties
    Private _Name As String
    Private _Description As String

    'Create a private reference to the task object
    Private _TaskObject As Interop.DTS.Task

    'Create other private variables to store the extended properties
    Private _LogFileName As String
    Private _LogToEventLog As Boolean
    Private _LogToFile As Boolean
    Private _HandledStepName As String
    Private _UseGlobals As Boolean

    'Friend variable for the UI to report whether or not it was cancelled    
    Friend bolUICancelled As Boolean

    Public Property Description() As String Implements 
           Interop.DTS.CustomTask.Description
        Get
            Return _Description

        End Get
        Set(ByVal Value As String)
            _Description = Value
        End Set
    End Property
    Public Property Name() As String Implements Interop.DTS.CustomTask.Name
        Get
            Return _Name
        End Get
        Set(ByVal Value As String)
            _Name = Value
        End Set
    End Property

    Public Sub Execute(ByVal pPackage As Object, ByVal pPackageEvents 
        As Object, ByVal pPackageLog As Object, ByRef pTaskResult 
            As Interop.DTS.DTSTaskExecResult) Implements 
                Interop.DTS.CustomTask.Execute

        Dim objPackage As Interop.DTS.Package
        Dim objTasks As Interop.DTS.Tasks
        Dim objStep As Interop.DTS.Step
        Dim objPackageLog As Interop.DTS.PackageLog

        Dim strSource As String
        Dim lngNumber As Long
        Dim strDescription As String
        Dim strErrorString As String

        Try
            'Cast the passed params into the local stronger typed variables
            objPackage = pPackage
            objPackageLog = pPackageLog

            'Make sure we have a precedence step
            Call EstablishPrecedence(objPackage)

            'Check the HandledStepName and Throw an Exception
            If Me.HandledStepName = "" Then
                Throw New System.Exception("Task cannot execute due to no 
                    Precedence Constraint being defined.")
            End If

            'If the Task is defined to drive from the global variables 
                we need to check
            Call ReadFromGlobalVariables(objPackage)

            'Get a Step Object Reference
            objStep = objPackage.Steps.Item(Me.HandledStepName)

            'Retrieve the Error Information
            Call objStep.GetExecutionErrorInfo(lngNumber, strSource, 
                strDescription)

            'Build the Error String
            strErrorString = Format(Now, "MM/dd/yyyy hh:mm:ss tt") & "|" & _
                             objPackage.Name & "|" & objStep.Name & "|" & _
                             " The Step, " & objStep.Description & _
                             " failed with the following error: " & _
                             lngNumber.ToString() & " - " & strSource & " - " & strDescription


            'Determine if you should write to a log file
            If Me.LogToFile And Trim(Me.LogFileName) = "" Then
                'Throw an exception due to the lack of a filename
                Throw New System.Exception("Task cannot log to file due to 
                    no log file being defined.")
            ElseIf Me.LogToFile And Trim(Me.LogFileName) <> "" Then
                'Write to the log file
                Dim objFile As System.IO.File
                Dim objStreamWriter As System.IO.StreamWriter
                objStreamWriter = objFile.AppendText(Me.LogFileName)

                'Close the StreamWriter and Clean up the fileobject
                objStreamWriter.WriteLine(strErrorString)
                objStreamWriter.Close()
                objStreamWriter = Nothing
                objFile = Nothing

            End If

            'Determine if you should write to the eventlog
            If Me.LogToEventLog Then
                Dim objEventLog As New EventLog()
                objEventLog.WriteEntry(".Net DTS Error Handler", strErrorString)
                objEventLog = Nothing
            End If

            'Return Success as this Execution was successful
            pTaskResult = Interop.DTS.DTSTaskExecResult.DTSTaskExecResult_Success

        Catch ex As Exception
            Throw New System.Exception("An Error occured during the 
                tasks execution.", ex)

            'Return Failure as this Execution was a failure
            pTaskResult = Interop.DTS.DTSTaskExecResult.DTSTaskExecResult_Failure

        End Try

    End Sub

    Public Property LogFileName() As String

        Get
            Return _LogFileName

        End Get
        Set(ByVal Value As String)
            _LogFileName = Value
        End Set
    End Property

    Public Property LogToFile() As Boolean
        Get
            Return _LogToFile

        End Get
        Set(ByVal Value As Boolean)
            _LogToFile = Value
        End Set
    End Property

    Public Property LogToEventLog() As Boolean
        Get
            Return _LogToEventLog

        End Get
        Set(ByVal Value As Boolean)
            _LogToEventLog = Value
        End Set
    End Property

    Public Property HandledStepName() As String
        Get
            Return _HandledStepName

        End Get
        Set(ByVal Value As String)
            _HandledStepName = Value
        End Set
    End Property

    Public Property UseGlobals() As Boolean
        Get
            Return _UseGlobals

        End Get
        Set(ByVal Value As Boolean)
            _UseGlobals = Value
        End Set
    End Property

    Public ReadOnly Property Properties() As Interop.DTS.Properties 
        Implements Interop.DTS.CustomTask.Properties
        Get
            'Declare variables for Properties collection and 
                ProviderClass to retrieve property info
            Dim objProperties As Interop.DTS.Properties
            Dim objPropProvider As Interop.DTS.PropertiesProviderClass = 
                New Interop.DTS.PropertiesProviderClass()

            'Using the reference to the existing task object, 
                get a reference to the task object 
            'to pass to the Properties Provider    
            objProperties = objPropProvider.GetPropertiesForObject(_TaskObject)

            'Perform some cleanup on the properties provider    
            objPropProvider = Nothing

            'Return the value of the properties collection    
            Return objProperties

        End Get
    End Property

    Public Sub Load(ByVal PropertyBag As Interop.DTS.PropertyBag) 
        Implements Interop.DTS.PersistPropertyBag.Load
        'Create all of the PropertyBag readers to read information from the 
        'PropertyBag into the module level variables

        _Name = PropertyBag.Read("Name")
        _Description = PropertyBag.Read("Description")
        _LogFileName = PropertyBag.Read("LogFileName")
        _LogToEventLog = PropertyBag.Read("LogToEventLog")
        _LogToFile = PropertyBag.Read("LogToFile")
        _HandledStepName = PropertyBag.Read("HandledStepName")
        _UseGlobals = PropertyBag.Read("UseGlobals")

    End Sub

    Public Sub Save(ByVal PropertyBag As Interop.DTS.PropertyBag) 
        Implements Interop.DTS.PersistPropertyBag.Save
        'Create all of the PropertyBag writers to persist information from the 
        'the module level variables into the property bag

        PropertyBag.Write("Name", _Name)
        PropertyBag.Write("Description", _Description)
        PropertyBag.Write("LogFileName", _LogFileName)
        PropertyBag.Write("LogToEventLog", _LogToEventLog)
        PropertyBag.Write("LogToFile", _LogToFile)
        PropertyBag.Write("HandledStepName", _HandledStepName)
        PropertyBag.Write("UseGlobals", _UseGlobals)

    End Sub

    Public Sub New()

    End Sub

    Public Sub Initialize(ByVal pTask As Interop.DTS.Task) Implements 
      Interop.DTS.CustomTaskUI.Initialize
        _TaskObject = pTask
    End Sub

    Protected Overrides Sub Finalize()
        MyBase.Finalize()
    End Sub

    Public Sub Edit(ByVal hwndParent As Integer) Implements 
      Interop.DTS.CustomTaskUI.Edit
        'Should Show a UI Here
        ShowPropertyUI()
    End Sub

    Public Sub GetUIInfo(ByRef pbstrToolTip As String, ByRef 
      pbstrDescription As String, ByRef plVersion As Integer, ByRef pFlags 
        As Interop.DTS.DTSCustomTaskUIFlags) Implements 
          Interop.DTS.CustomTaskUI.GetUIInfo
            'Not Implemented
    End Sub

    Public Sub Delete(ByVal hwndParent As Integer) Implements 
      Interop.DTS.CustomTaskUI.Delete
        _TaskObject = Nothing

    End Sub

    Public Sub Help(ByVal hwndParent As Integer) Implements 
      Interop.DTS.CustomTaskUI.Help
        'Not Implemented
    End Sub

    Public Sub CreateCustomToolTip(ByVal hwndParent As Integer, 
      ByVal x As Integer, ByVal y As Integer, ByRef plTipWindow 
        As Integer) Implements Interop.DTS.CustomTaskUI.CreateCustomToolTip
          'Not Implemented
    End Sub

    Public Sub New2(ByVal hwndParent As Integer) Implements 
      Interop.DTS.CustomTaskUI.New
        'In order to implement the New Method we needed to declare this way

        'Preset the Settings for the task
        Me.LogToEventLog = True
        Me.LogToFile = True
        Me.LogFileName = ""
        Me.UseGlobals = True

        'Should Show a UI Here
        ShowPropertyUI()

    End Sub

    Private Sub ShowPropertyUI()
        'Handles the UI Property Page Display

        Dim objForm As New frmProperty()
        Dim objPackage As Object
        Dim objTasks As Object

        objForm.objCustomTask = Me
        objForm.objMyTask = _TaskObject

        Try

            'Construct references to the Tasks Collection and the Package Object
            objTasks = _TaskObject.Parent
            objPackage = objTasks.Parent

            'Determine if the global variables exist for the Error Handler
            Call CheckGlobalVariables(objPackage)

            'Read data from the global variables and assign to properties
            Call ReadFromGlobalVariables(objPackage)

            'Check for a precedenceconstraint
            Call EstablishPrecedence(objPackage)

            'Set a Friend declared boolean so we will know how the user 
               left the UI
            bolUICancelled = True

            'Tell the Form to load the properties before you show it
            objForm.GetProperties()

            'Show the Form
            objForm.ShowDialog()

            'Check to see how the user cancelled 
            'Save the Properties to Global Variables if appropriate
            If Not bolUICancelled Then
                Call SaveToGlobalVariables(objPackage)
            End If

        Catch ex As Exception
            MsgBox(ex.Source & " - " & ex.Message, MsgBoxStyle.Critical, 
               ".NET Error Handler")
        End Try

    End Sub

    Private Sub EstablishPrecedence(ByVal pobjPackage As 
       Interop.DTS.Package)

        Dim objStep As Interop.DTS.Step
        Dim objPC As Interop.DTS.PrecedenceConstraint

        'Examine the steps for matching TaskNames
        'Evaluated the number of precedence constraints
        For Each objStep In pobjPackage.Steps
            If objStep.TaskName = _Name Then
                If objStep.PrecedenceConstraints.Count >= 1 Then
                    'Establish the Precedence Ref

                    'Get a reference to the precedence constraint and 
                         determine 
                    'the step info for the handler
                    objPC = objStep.PrecedenceConstraints.Item(1)
                    Me.HandledStepName = objPC.StepName

                    'Dynamically Build a Task Description 
                    Me.Description() = "Handler for " & 
                        pobjPackage.Steps.Item(objPC.StepName).Description

                ElseIf objStep.PrecedenceConstraints.Count < 1 Then
                    Me.HandledStepName = ""

                    Exit For
                End If

                Exit For

            End If

        Next

        objStep = Nothing
        objPC = Nothing

    End Sub

    Private Sub CheckGlobalVariables(ByVal pobjPackage As Interop.DTS.Package)
        Dim objGlobalVariables As Interop.DTS.GlobalVariables = pobjPackage.GlobalVariables
        Dim objGV As Interop.DTS.GlobalVariable2
        Dim bolErrorLogFileFound As Boolean
        Dim bolErrorLogToEventLogFound As Boolean
        Dim bolErrorLogToFileFound As Boolean
        Dim bolShowMessage As Boolean

        'Check for the Existence of the Global Variables

        For Each objGV In objGlobalVariables
            Select Case UCase(objGV.Name)
                Case "GSTRERRORLOGFILE"
                    bolErrorLogFileFound = True
                Case "GBOLERRORLOGTOEVENTLOG"
                    bolErrorLogToEventLogFound = True
                Case "GBOLERRORLOGTOFILE"
                    bolErrorLogToFileFound = True
                Case Else
                    'Not important

            End Select

        Next

        'If the variable wasn't found then ADD it to the package 
            globalvariables collection
        If bolErrorLogFileFound = False Then
            objGlobalVariables.AddGlobalVariable("gstrErrorLogFile", 
               CStr("c:\errorhandlerlog.log"))
            bolShowMessage = True
        End If

        'If the variable wasn't found then ADD it to the package 
            globalvariables collection
        If bolErrorLogToEventLogFound = False Then
            objGlobalVariables.AddGlobalVariable("gbolErrorLogToEventLog", 
                CBool(True))
            bolShowMessage = True
        End If

        'If the variable wasn't found then ADD it to the package 
            globalvariables collection
        If bolErrorLogToFileFound = False Then
            objGlobalVariables.AddGlobalVariable("gbolErrorLogToFile", 
                CBool(True))
            bolShowMessage = True
        End If

        'If a global variable was added, let the user know
        If bolShowMessage Then
            MsgBox("Global Variables have been added to this package to 
              allow all Error Handlers to reference one setting." & vbCrLf 
                & "Please indicate in individual Error Handler tasks 
                  whether or not the global variables are to be used.", 
                    MsgBoxStyle.Information, ".NET Error Handler: Global 
                      Variables Added")
        End If

    End Sub

    Private Sub SaveToGlobalVariables(ByVal pobjPackage As Interop.DTS.Package)
        Dim objGlobalVariables As Interop.DTS.GlobalVariables = pobjPackage.GlobalVariables
        Dim objGlobalVariable As Interop.DTS.GlobalVariable2


        'If the task uses the values in the global variables then the 
        'values entered in 
        'the UI should be persisted to the GlobalVariables Collection
        If Me.UseGlobals Then
            'Oddity: The only way to get the globals to be 
            'accessible as their correct types (i.e. String and Booleand) 
            'is to remove and re-add them
            objGlobalVariables.Remove("gstrErrorLogFile")
            objGlobalVariables.AddGlobalVariable("gstrErrorLogFile", 
               CStr(Me.LogFileName.ToString))

            objGlobalVariables.Remove("gbolErrorLogToEventLog")
            objGlobalVariables.AddGlobalVariable("gbolErrorLogToEventLog", 
               CBool(Me.LogToEventLog))

            objGlobalVariables.Remove("gbolErrorLogToFile")
            objGlobalVariables.AddGlobalVariable("gbolErrorLogToFile", 
                CBool(Me.LogToFile))

            'Let the user know that this action was performed
            MsgBox("The .NET Error Handler global variables were updated", 
               MsgBoxStyle.Information, ".NET Error Handler")

        End If

        'Clean up and relegate to the GC
        objGlobalVariable = Nothing
        objGlobalVariables = Nothing

    End Sub

    Private Sub ReadFromGlobalVariables(ByVal pobjPackage As Interop.DTS.Package)
        Dim objGlobalVariables As Interop.DTS.GlobalVariables = 
           pobjPackage.GlobalVariables

        'If the task uses the values in the global variables then the values entered in 
        'the GlobalVariables should be assigned to the Task's properties
        If Me.UseGlobals Then
            Me.LogFileName = objGlobalVariables.Item("gstrErrorLogFile").Value
            Me.LogToEventLog = objGlobalVariables.Item("gbolErrorLogToEventLog").Value
            Me.LogToFile = objGlobalVariables.Item("gbolErrorLogToFile").Value
        End If

    End Sub


End Class

FrmProperty.VB File Contents

Public Class frmProperty
    Inherits System.Windows.Forms.Form

    'Public Variable Declarations so the Task can hand off 
    'references to itself (as a Task object and as a CustomTask object)
    Public objMyTask As Interop.DTS.Task
    Public objCustomTask As dotNetErrorHandler.EHTask

    'Private Globals for references to the 
    'Tasks Collection and the Package Object
    Private objTasks As Interop.DTS.Tasks
    Private objPackage As Interop.DTS.Package

#Region " Windows Form Designer generated code "

    Public Sub New()
        MyBase.New()

        'This call is required by the Windows Form Designer.
        InitializeComponent()

        'Add any initialization after the InitializeComponent() call

    End Sub

    'Form overrides dispose to clean up the component list.
    Protected Overloads Overrides Sub Dispose(ByVal disposing As Boolean)
        If disposing Then

            If Not (components Is Nothing) Then
                components.Dispose()
            End If
        End If
        MyBase.Dispose(disposing)
    End Sub

    'Required by the Windows Form Designer
    Private components As System.ComponentModel.IContainer

    'NOTE: The following procedure is required by the Windows Form Designer
    'It can be modified using the Windows Form Designer.  
    'Do not modify it using the code editor.
    Friend WithEvents txtName As System.Windows.Forms.TextBox
    Friend WithEvents txtDescription As System.Windows.Forms.TextBox
    Friend WithEvents Label2 As System.Windows.Forms.Label
    Friend WithEvents Label3 As System.Windows.Forms.Label
    Friend WithEvents chkLogToEvent As System.Windows.Forms.CheckBox
    Friend WithEvents chkLogToFile As System.Windows.Forms.CheckBox
    Friend WithEvents txtFileName As System.Windows.Forms.TextBox
    Friend WithEvents btnFileDialog As System.Windows.Forms.Button
    Friend WithEvents btnOkay As System.Windows.Forms.Button
    Friend WithEvents btnCancel As System.Windows.Forms.Button
    Friend WithEvents txtStepName As System.Windows.Forms.TextBox
    Friend WithEvents ckhUseGlobals As System.Windows.Forms.CheckBox
    Friend WithEvents lblVersion As System.Windows.Forms.Label
    <System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent()
        Me.lblVersion = New System.Windows.Forms.Label()
        Me.txtName = New System.Windows.Forms.TextBox()
        Me.txtDescription = New System.Windows.Forms.TextBox()
        Me.Label2 = New System.Windows.Forms.Label()
        Me.Label3 = New System.Windows.Forms.Label()
        Me.chkLogToEvent = New System.Windows.Forms.CheckBox()
        Me.chkLogToFile = New System.Windows.Forms.CheckBox()
        Me.txtFileName = New System.Windows.Forms.TextBox()
        Me.btnFileDialog = New System.Windows.Forms.Button()
        Me.btnOkay = New System.Windows.Forms.Button()
        Me.btnCancel = New System.Windows.Forms.Button()
        Me.txtStepName = New System.Windows.Forms.TextBox()
        Me.ckhUseGlobals = New System.Windows.Forms.CheckBox()
        Me.SuspendLayout()
        '
        'lblVersion
        '
        Me.lblVersion.Location = New System.Drawing.Point(24, 200)
        Me.lblVersion.Name = "lblVersion"
        Me.lblVersion.Size = New System.Drawing.Size(240, 24)
        Me.lblVersion.TabIndex = 0
        '
        'txtName
        '
        Me.txtName.Location = New System.Drawing.Point(304, 208)
        Me.txtName.Name = "txtName"
        Me.txtName.Size = New System.Drawing.Size(72, 20)
        Me.txtName.TabIndex = 1
        Me.txtName.Text = "<Task Name>"
        Me.txtName.Visible = False
        '
        'txtDescription
        '
        Me.txtDescription.Location = New System.Drawing.Point(96, 16)
        Me.txtDescription.Name = "txtDescription"
        Me.txtDescription.Size = New System.Drawing.Size(408, 20)
        Me.txtDescription.TabIndex = 3
        Me.txtDescription.Text = "<Task Desc>"
        '
        'Label2
        '
        Me.Label2.Location = New System.Drawing.Point(16, 16)
        Me.Label2.Name = "Label2"
        Me.Label2.Size = New System.Drawing.Size(104, 24)
        Me.Label2.TabIndex = 2
        Me.Label2.Text = "Description :"
        '
        'Label3
        '
        Me.Label3.Location = New System.Drawing.Point(16, 56)
        Me.Label3.Name = "Label3"
        Me.Label3.Size = New System.Drawing.Size(256, 16)
        Me.Label3.TabIndex = 5
        Me.Label3.Text = "Handled Step :"
        '
        'chkLogToEvent
        '
        Me.chkLogToEvent.Location = New System.Drawing.Point(96, 88)
        Me.chkLogToEvent.Name = "chkLogToEvent"
        Me.chkLogToEvent.Size = New System.Drawing.Size(256, 24)
        Me.chkLogToEvent.TabIndex = 6
        Me.chkLogToEvent.Text = "Log Error to the Windows Event Log"
        '
        'chkLogToFile
        '
        Me.chkLogToFile.Location = New System.Drawing.Point(96, 120)
        Me.chkLogToFile.Name = "chkLogToFile"
        Me.chkLogToFile.Size = New System.Drawing.Size(256, 24)
        Me.chkLogToFile.TabIndex = 7
        Me.chkLogToFile.Text = "Log Error to the Following File "
        '
        'txtFileName
        '
        Me.txtFileName.Location = New System.Drawing.Point(272, 120)
        Me.txtFileName.Name = "txtFileName"
        Me.txtFileName.Size = New System.Drawing.Size(208, 20)
        Me.txtFileName.TabIndex = 8
        Me.txtFileName.Text = "c:\dts.log"
        '
        'btnFileDialog
        '
        Me.btnFileDialog.Location = New System.Drawing.Point(480, 120)
        Me.btnFileDialog.Name = "btnFileDialog"
        Me.btnFileDialog.Size = New System.Drawing.Size(24, 24)
        Me.btnFileDialog.TabIndex = 9
        Me.btnFileDialog.Text = "..."
        '
        'btnOkay
        '
        Me.btnOkay.Location = New System.Drawing.Point(352, 200)
        Me.btnOkay.Name = "btnOkay"
        Me.btnOkay.TabIndex = 10
        Me.btnOkay.Text = "OK"
        '
        'btnCancel
        '
        Me.btnCancel.DialogResult = System.Windows.Forms.DialogResult.Cancel
        Me.btnCancel.Location = New System.Drawing.Point(432, 200)
        Me.btnCancel.Name = "btnCancel"
        Me.btnCancel.TabIndex = 11
        Me.btnCancel.Text = "Cancel"
        '
        'txtStepName
        '
        Me.txtStepName.Location = New System.Drawing.Point(96, 56)
        Me.txtStepName.Name = "txtStepName"
        Me.txtStepName.ReadOnly = True
        Me.txtStepName.Size = New System.Drawing.Size(408, 20)
        Me.txtStepName.TabIndex = 12
        Me.txtStepName.Text = "<Handled Step Description (Name)>"
        '
        'ckhUseGlobals
        '
        Me.ckhUseGlobals.Location = New System.Drawing.Point(96, 152)
        Me.ckhUseGlobals.Name = "ckhUseGlobals"
        Me.ckhUseGlobals.Size = New System.Drawing.Size(408, 40)
        Me.ckhUseGlobals.TabIndex = 13
        Me.ckhUseGlobals.Text = "Use Global Variables 
           (Properties will be read from the Globals at run-time)"
        '
        'frmProperty
        '
        Me.AutoScaleBaseSize = New System.Drawing.Size(5, 13)
        Me.CancelButton = Me.btnCancel
        Me.ClientSize = New System.Drawing.Size(512, 247)
        Me.Controls.AddRange(New System.Windows.Forms.Control() 
          {Me.ckhUseGlobals, Me.txtStepName, Me.btnCancel, Me.btnOkay, 
            Me.btnFileDialog, Me.txtFileName, Me.chkLogToFile, 
                Me.chkLogToEvent, Me.Label3, Me.txtDescription, Me.Label2, 
                    Me.txtName, Me.lblVersion})
        Me.FormBorderStyle = System.Windows.Forms.FormBorderStyle.FixedDialog
        Me.MaximizeBox = False
        Me.MinimizeBox = False
        Me.Name = "frmProperty"
        Me.SizeGripStyle = System.Windows.Forms.SizeGripStyle.Hide
        Me.Text = ".NET Error Handler Task Properties"
        Me.ResumeLayout(False)

    End Sub

#End Region

    Private Sub frmProperty_Load(ByVal sender As System.Object, ByVal e As 
      System.EventArgs) Handles MyBase.Load
        'There is nothing to do here because we invoke our own custom methods
        Me.CenterToParent()

    End Sub

#Region " Our Own Custom Methods "

    Public Sub GetProperties()
        'Establish the Package and Task Reference first
        Dim objStep As Interop.DTS.Step
        Dim objPC As Interop.DTS.PrecedenceConstraint
        Dim objTask As Object

        Dim strHandledStepInfo As String
        Dim strDescription As String
        Dim strFilename As String
        Dim strVersion As String

        'Construct references to the Tasks Collection and the Package Object
        objTasks = objMyTask.Parent
        objPackage = objTasks.Parent

        'Get the CustomTasks Description
        strDescription = objCustomTask.Description

        'Examine the steps for matching TaskNames
        'Evaluated the number of precedence constraints
        For Each objStep In objPackage.Steps
            If objStep.TaskName = objMyTask.Name Then
                If objStep.PrecedenceConstraints.Count > 1 Then
                    MsgBox("This task can only use 1 precedence 
                        constraint." & vbCrLf & _
                    "The first will be the one handled by the task.", 
                        MsgBoxStyle.Critical, ".NET Error Handler")

                ElseIf objStep.PrecedenceConstraints.Count < 1 Then
                    MsgBox("This task requires that a precedence " & _
                            "constraint be defined.", MsgBoxStyle.Critical,
                               ".NET Error Handler")
                    objCustomTask.HandledStepName = ""
                    strHandledStepInfo = "<No Preceding Step Defined>"
                    Exit For
                End If

                'Get a reference to the precedence constraint and determine 
                'the step info for the handler
                objPC = objStep.PrecedenceConstraints.Item(1)
                objCustomTask.HandledStepName = objPC.StepName

                strHandledStepInfo = 
                    objPackage.Steps.Item(objPC.StepName).Description & _
                                    " (" & objPC.StepName & ")"

                'Establish a New Description if the predecessor step exists
                strDescription = "Handler for " & 
                    objPackage.Steps.Item(objPC.StepName).Description

                Exit For

            End If

        Next

        'Map properties to UI elements
        Me.txtDescription.Text = strDescription
        Me.txtName.Text = objCustomTask.Name

        If objCustomTask.LogFileName = "" Then
            Me.txtFileName.Text = strFilename
        ElseIf strFilename = "" Then
            Me.txtFileName.Text = objCustomTask.LogFileName
        Else
            Me.txtFileName.Text = strFilename
        End If

        Me.chkLogToEvent.Checked = objCustomTask.LogToEventLog
        Me.chkLogToFile.Checked = objCustomTask.LogToFile
        Me.txtStepName.Text = strHandledStepInfo
        Me.ckhUseGlobals.Checked = objCustomTask.UseGlobals

        'Establish the Version information - helpful for knowing 
            what version is deployed
        strVersion = "Version " & 
          System.Reflection.Assembly.GetExecutingAssembly.GetName().Version.Major.To
             String()
        strVersion = strVersion & "." & 
          System.Reflection.Assembly.GetExecutingAssembly.GetName().Version.Minor.To
             String
        strVersion = strVersion & "." & 
          System.Reflection.Assembly.GetExecutingAssembly.GetName().Version.Build.To
              String

        Me.lblVersion.Text = strVersion

    End Sub
    Public Sub SaveProperties()

        'Map Form Elements to Task Properties
        objCustomTask.Description = Me.txtDescription.Text
        objCustomTask.Name = Me.txtName.Text
        objCustomTask.LogFileName = Me.txtFileName.Text
        objCustomTask.LogToEventLog = Me.chkLogToEvent.Checked
        objCustomTask.LogToFile = Me.chkLogToFile.Checked
        objCustomTask.UseGlobals = Me.ckhUseGlobals.Checked

    End Sub

#End Region

    Private Sub frmProperty_Closing(ByVal sender As Object, ByVal e As 
      System.ComponentModel.CancelEventArgs) Handles MyBase.Closing
        objMyTask = Nothing
        objCustomTask = Nothing

        'Clean up the global private references
        objTasks = Nothing
        objPackage = Nothing
    End Sub

    Private Sub btnOkay_Click(ByVal sender As System.Object, ByVal e As 
      System.EventArgs) Handles btnOkay.Click
        objCustomTask.bolUICancelled = False
        Me.SaveProperties()
        Me.Close()
    End Sub

    Private Sub btnCancel_Click(ByVal sender As System.Object, ByVal e As 
      System.EventArgs) Handles btnCancel.Click
        Me.Close()
    End Sub

    Private Sub btnFileDialog_Click(ByVal sender As System.Object, ByVal e 
      As System.EventArgs) Handles btnFileDialog.Click

        Dim objFileDialog As New Windows.Forms.SaveFileDialog()
        'Configure the FileDialog
        objFileDialog.DefaultExt = ".log"
        objFileDialog.Filter = "Log Files (*.log)|*.log"

        objFileDialog.FileName = Me.txtFileName.Text
        objFileDialog.Title = ".NET Error Handler : Select Log File"

        objFileDialog.ShowDialog()

        If objFileDialog.FileName() <> "" Then
            Me.txtFileName.Text = objFileDialog.FileName()

        End If

        objFileDialog = Nothing


    End Sub


End Class

FrmProperty.Resx File Contents

<?xml version="1.0" encoding="utf-8"?>
<root>
  <xsd:schema id="root" xmlns="" 
    xmlns:xsd="http://www.w3.org/2001/XMLSchema" 
      xmlns:msdata="urn:schemas-microsoft-com:xml-msdata">
    <xsd:element name="root" msdata:IsDataSet="true">
      <xsd:complexType>
        <xsd:choice maxOccurs="unbounded">
          <xsd:element name="data">
            <xsd:complexType>
              <xsd:sequence>
                <xsd:element name="value" type="xsd:string" minOccurs="0" 
                    msdata:Ordinal="1" />
                <xsd:element name="comment" type="xsd:string" 
                    minOccurs="0" msdata:Ordinal="2" />
              </xsd:sequence>
              <xsd:attribute name="name" type="xsd:string" msdata:Ordinal="1" />
              <xsd:attribute name="type" type="xsd:string" msdata:Ordinal="3" />
              <xsd:attribute name="mimetype" type="xsd:string" msdata:Ordinal="4" />
            </xsd:complexType>
          </xsd:element>
          <xsd:element name="resheader">
            <xsd:complexType>
              <xsd:sequence>
                <xsd:element name="value" type="xsd:string" minOccurs="0" 
                    msdata:Ordinal="1" />
              </xsd:sequence>
              <xsd:attribute name="name" type="xsd:string" use="required" />
            </xsd:complexType>
          </xsd:element>
        </xsd:choice>
      </xsd:complexType>
    </xsd:element>
  </xsd:schema>
  <resheader name="resmimetype">
    <value>text/microsoft-resx</value>
  </resheader>
  <resheader name="version">
    <value>1.3</value>
  </resheader>
  <resheader name="reader">
    <value>System.Resources.ResXResourceReader, System.Windows.Forms, 
      Version=1.0.3300.0, Culture=neutral, 
        PublicKeyToken=b77a5c561934e089</value>
  </resheader>
  <resheader name="writer">
    <value>System.Resources.ResXResourceWriter, System.Windows.Forms, 
      Version=1.0.3300.0, Culture=neutral, 
        PublicKeyToken=b77a5c561934e089</value>
  </resheader>
  <data name="lblVersion.Modifiers" type="System.CodeDom.MemberAttributes, 
    System, Version=1.0.3300.0, Culture=neutral, 
      PublicKeyToken=b77a5c561934e089">
    <value>Assembly</value>
  </data>
  <data name="txtName.Modifiers" type="System.CodeDom.MemberAttributes, 
    System, Version=1.0.3300.0, Culture=neutral, 
      PublicKeyToken=b77a5c561934e089">
    <value>Assembly</value>
  </data>
  <data name="txtDescription.Modifiers" 
    type="System.CodeDom.MemberAttributes, System, Version=1.0.3300.0, 
      Culture=neutral, PublicKeyToken=b77a5c561934e089">
    <value>Assembly</value>
  </data>
  <data name="Label2.Modifiers" type="System.CodeDom.MemberAttributes, 
    System, Version=1.0.3300.0, Culture=neutral, 
      PublicKeyToken=b77a5c561934e089">
    <value>Assembly</value>
  </data>
  <data name="Label3.Modifiers" type="System.CodeDom.MemberAttributes, 
    System, Version=1.0.3300.0, Culture=neutral, 
      PublicKeyToken=b77a5c561934e089">
    <value>Assembly</value>
  </data>
  <data name="chkLogToEvent.Modifiers" 
    type="System.CodeDom.MemberAttributes, System, Version=1.0.3300.0, 
      Culture=neutral, PublicKeyToken=b77a5c561934e089">
    <value>Assembly</value>
  </data>
  <data name="chkLogToFile.Modifiers" 
    type="System.CodeDom.MemberAttributes, System, Version=1.0.3300.0, 
      Culture=neutral, PublicKeyToken=b77a5c561934e089">
    <value>Assembly</value>
  </data>
  <data name="txtFileName.Modifiers" 
    type="System.CodeDom.MemberAttributes, System, Version=1.0.3300.0, 
      Culture=neutral, PublicKeyToken=b77a5c561934e089">
    <value>Assembly</value>
  </data>
  <data name="btnFileDialog.Modifiers" 
    type="System.CodeDom.MemberAttributes, System, Version=1.0.3300.0, 
      Culture=neutral, PublicKeyToken=b77a5c561934e089">
    <value>Assembly</value>
  </data>
  <data name="btnOkay.Modifiers" type="System.CodeDom.MemberAttributes, 
    System, Version=1.0.3300.0, Culture=neutral, 
      PublicKeyToken=b77a5c561934e089">
    <value>Assembly</value>
  </data>
  <data name="btnCancel.Modifiers" type="System.CodeDom.MemberAttributes, 
    System, Version=1.0.3300.0, Culture=neutral, 
      PublicKeyToken=b77a5c561934e089">
    <value>Assembly</value>
  </data>
  <data name="txtStepName.Modifiers" 
    type="System.CodeDom.MemberAttributes, System, Version=1.0.3300.0, 
      Culture=neutral, PublicKeyToken=b77a5c561934e089">
    <value>Assembly</value>
  </data>
  <data name="ckhUseGlobals.Modifiers" 
    type="System.CodeDom.MemberAttributes, System, Version=1.0.3300.0, 
      Culture=neutral, PublicKeyToken=b77a5c561934e089">
    <value>Assembly</value>
  </data>
  <data name="$this.Name">
    <value>frmProperty</value>
  </data>
</root>

dotNetErrorHandler.VBPROJ File Contents

<VisualStudioProject>
    <VisualBasic
        ProjectType = "Local"
        ProductVersion = "7.0.9466"
        SchemaVersion = "1.0"
        ProjectGuid = "{C136AEE2-5D34-4B77-8F71-7DACB8EF1F28}"
    >
        <Build>
            <Settings
                ApplicationIcon = ""
                AssemblyKeyContainerName = ""
                AssemblyName = "dotNetErrorHandler"
                AssemblyOriginatorKeyFile = ""
                AssemblyOriginatorKeyMode = "None"
                DefaultClientScript = "JScript"
                DefaultHTMLPageLayout = "Grid"
                DefaultTargetSchema = "IE50"
                DelaySign = "false"
                OutputType = "Library"
                OptionCompare = "Binary"
                OptionExplicit = "On"
                OptionStrict = "Off"
                RootNamespace = "dotNetErrorHandler"
                StartupObject = ""
            >
                <Config
                    Name = "Debug"
                    BaseAddress = "285212672"
                    ConfigurationOverrideFile = ""
                    DefineConstants = ""
                    DefineDebug = "true"
                    Def race = "true"
                    DebugSymbols = "true"
                    IncrementalBuild = "true"
                    Optimize = "false"
                    OutputPath = "bin\"
                    RegisterForComInterop = "false"
                    RemoveIntegerChecks = "false"
                    TreatWarningsAsErrors = "false"
                    WarningLevel = "1"
                />
                <Config
                    Name = "Release"
                    BaseAddress = "285212672"
                    ConfigurationOverrideFile = ""
                    DefineConstants = ""
                    DefineDebug = "false"
                    Def race = "true"
                    DebugSymbols = "false"
                    IncrementalBuild = "false"
                    Optimize = "true"
                    OutputPath = "bin\"
                    RegisterForComInterop = "false"
                    RemoveIntegerChecks = "false"
                    TreatWarningsAsErrors = "false"
                    WarningLevel = "1"
                />
            </Settings>
            <References>
                <Reference
                    Name = "System"
                    AssemblyName = "System"
                />
                <Reference
                    Name = "System.Data"
                    AssemblyName = "System.Data"
                />
                <Reference
                    Name = "System.XML"
                    AssemblyName = "System.Xml"
                />
                <Reference
                    Name = "Interop.DTS"
                    AssemblyName = "Interop.DTS"
                    HintPath = "Interop.DTS.dll"
                />
                <Reference
                    Name = "System.Drawing"
                    AssemblyName = "System.Drawing"
                    HintPath = "..\..\..\..\WINDOWS\Microsoft.NET\Framework
                        \v1.0.3705\System.Drawing.dll"
                />
                <Reference
                    Name = "System.Windows.Forms"
                    AssemblyName = "System.Windows.Forms"
                    HintPath = "..\..\..\..\WINDOWS\Microsoft.NET\Framework
                        \v1.0.3705\System.Windows.Forms.dll"
                />
            </References>
            <Imports>
                <Import Namespace = "Microsoft.VisualBasic" />
                <Import Namespace = "System" />
                <Import Namespace = "System.Collections" />
                <Import Namespace = "System.Data" />
                <Import Namespace = "System.Diagnostics" />
            </Imports>
        </Build>
        <Files>
            <Include>
                <File
                    RelPath = "AssemblyInfo.vb"
                    SubType = "Code"
                    BuildAction = "Compile"
                />
                <File
                    RelPath = "EHTask.vb"
                    SubType = "Code"
                    BuildAction = "Compile"
                />
                <File
                    RelPath = "frmProperty.vb"
                    SubType = "Form"
                    BuildAction = "Compile"
                />
                <File
                    RelPath = "frmProperty.resx"
                    DependentUpon = "frmProperty.vb"
                    BuildAction = "EmbeddedResource"
                />
            </Include>
        </Files>
    </VisualBasic>
</VisualStudioProject>
Show:
© 2014 Microsoft