Using the Object Mode
Let's return to the data mart population scenario, which we discussed in the April issue and which used the DTS Package Designer. This article implements the solution with VB6 to illustrate Connections, Steps, Constraints, and DataPumpTask, DataDrivenQueryTask, and ExecuteSQLTask. Here's the scenario: A large corporation needs to combine the employee data from two subsidiary companies into one data mart and refresh that data weekly. To simulate the subsidiary companies, the example uses the Employee tables from two SQL Server sample databases, Northwind and Pubs (for more information about SQL Server's sample databases, see Michael Otey's SQL Seven column, page 80). A new database called HRMart represents the parent company's data mart. Listing 1 details the HRMart schema.
Setting Up the Environment
To begin, you must create a new VB project and add a reference to the DTS Package object model (we assume that you know VB6). Create a new Standard EXE project called HRMartExample. After the project exists, add the object reference. The primary OLE Automation interface in this ex-ample is the Micro-soft DTSPackage Ob- ject Library, which Screen 1 shows.
To enhance the readability of the VB code by using all available DTS enumeration constants, also add the Microsoft DTSDataPump Scripting Object Library. The HRMartExample application doesn't have a user interface, so remove the default form from the project and replace it with a new module. Let's call the module HRMartPackage; it will contain all your code.
Listing 2, page 48, details the module's declarations and main subroutine. Starting with the declarations, use the moPackage variable to reference the Package throughout the module. Next, establish an enumeration of the Connection IDs to use throughout the module, EConnectionIds. The ID uniquely identifies each Connection. Using the enumeration lets you enforce the uniqueness and still provide a meaningful name for referencing the Connections. The final declarations serve as placeholders for SQL Server logon information. Customize these constants to match your SQL Server environment.
In addition, Listing 2 shows the Main subroutine, the shell where you assemble and execute the Package. To begin assembling the Package, create and initialize a new Package object.
Package Creation and Initialization
To start, create, and initialize a Package object by calling the CreatePackage subroutine, which Listing 3, page 49, shows, and detail the Package. Then, assign it a name—in this case HRMartRefresh. The Package lets you log its completion status in the NT Event Log via the WriteCompletionStatusTo-NTEventLog property. You can adjust and adapt the Package to a specific execution environment by tuning the Package-PriorityClass and MaxConcurrentSteps properties. The PackagePriorityClass property establishes the Package's Win32 process priority. You can use the DTSPriorityClass enumeration constants to set this priority.
The MaxConcurrentSteps property controls the number of Steps that the Package can execute concurrently. As you tune this value, remember that each Step runs in its own thread, so setting this value larger than the number of CPUs in the computer won't improve performance.
The LineageOptions and RepositoryMetadataOptions properties control Package interaction with the Microsoft Repository. The LineageOptions property tells the Package how and whether to use the Repository; the DTSLineage enumeration constants control the property's values. The RepositoryMetadataOptions property controls how a Package is saved to the Repository; the DTSReposMetadataOptions enumeration constants control the property's values.
Another function of the Package object is to handle the transaction model; the UseTransaction property controls transaction usage. You control a Package's TransactionIsolationLevel by setting that property to one of the enumeration constants contained in DTSIsoLevel; its default value is Read-Committed. Last, you use the AutoCommitTransaction property to define transactional control. This property tells the Package whether to implicitly or explicitly commit an active transaction upon completing execution. Next, you can create the Connections to the various data stores.
Adding Connections
This example needs three Connections, one each to represent the parent and two subsidiary companies. The Con- nections are named PubsData, NorthwindData, and HRMart-Data and have ConnectionIds of 1, 2, and 3, respectively. To create the new Connections and add them to the Package, the Main subroutine in Listing 2 calls the AddConnection subroutine in Listing 4, page 49. The Connection object gives the Package the OLE DB service provider's definition. The Connections collection contains all the Connection objects accessible to the Package. To create a new Connection object, supply the class name of the OLE DB provider. Then, assign the ID property a unique numeric value.
The Connection object also contains several other attributes. The Reusable property determines whether multiple Steps can share the Connection. The ConnectImmediate property controls the Connection initiation. When the value is true, the Connection to the data store occurs when the Package starts running; when the value is false, the Connection doesn't start until a Step referencing it executes.
Other Connection properties describe the data store's environment information. With the Microsoft OLE DB property for SQL Server, the DataSource property identifies the server that SQL Server is on. For other providers, this value may differ. For example, for Oracle or Informix providers, this property may refer to the instance name. The UserID, Password, and UseTrustedConnection properties provide Connection security information.
The last property to implement is the Catalog, which identifies the database that the Connection initially occurs in. The ConnectionProperties specify and initialize additional attributes that are identifiable to the OLE DB provider. The example relies on the default settings of the SQL Server provider.
After you initialize the Connection object, add it to the Package's Connections collection. After you've added all three Connections, create the Tasks and Transformations the Package will perform. The first Task to add is an ExecuteSQLTask object to delete the HRMart..Employee data.
Adding an ExecuteSQLTask
An ExecuteSQLTask object is a specialized CustomTask object that lets a Package execute a SQL statement on a given Connection. To create a new Task, execute the New method of the Tasks collection and pass it the class name of the CustomTask to create. The purpose of this task is to clear the employee file before performing the transformation. To add the ExecuteSQLTask to the Package, call the Task-HRMartPurge subroutine, which Listing 5 shows.
First, create a new ExecuteSQLTask by calling the Tasks.New method and passing it the class name, DTSExecuteSQLTask. Next, set the properties; the Task's name is HRMartPurgeTask. The SQLStatement property contains the SQL statement for the Task to perform. In this case, the SQL statement is DELETE FROM Employee. A Task needs a Connection in order to execute; this Task uses the HRMartData Connection you defined using the enumeration constant EConnectionIds. Next, set the timeout value to establish how long the Task will wait for the SQL statement to complete execution.
Finally, using the Tasks collection's Add method, add ExecuteSQLTask to the Package. The next Task to add is the DataPumpTask, which transforms the Pubs..Employee data and inserts it into HRMart..Employee table.
In the next issue, we'll continue discussing the object model and complete the assignment by implementing the DataPump-Task, the DataDrivenQueryTask, Steps, and Constraints.
End of Article
Prev. page
1
[2]
next page -->
