Now that we have defined the source and destination data sets, we define how the columns map between the two. We map the columns on the Transformations tab of the Data Transformation Properties dialog box, which Screen 5, page 42, shows. By default, DTS maps each source column to a destination column in column order. If the source and destination columns don't match, we need to unmap the defaults and remap to the correct destinations. Next, we have to choose how to map our data. We can use the Column Copy option to remap each source column to a destination column. This method is the default. We can use the ActiveX script to map the individual columns. Or, we can use a combination of the Column Copy option and the ActiveX script. For this example, we'll use the combination method.
Before we add our transformation, we must delete the default column mappings by selecting and deleting each transformation line between the source and destination tables. For our example, we must unmap all the defaults. Our source query doesn't account for all the destination columns, so we have to give some destination columns default values. Next, we redefine the mappings for fname, minit, and lname by selecting them in the source table, selecting their corresponding columns in the destination table, and clicking New to create a New Transformation of type Copy Column.
To verify that we mapped the columns correctly, we double-click the transformation line between the two tables or right-click the transformation line, and click Properties. Screen 6 shows the Column Order dialog box in which we can adjust the mappings. The Column Order dialog box displays only the columns we select on the Transformations tab. We mapped the three columns with one transformation; however, we could have mapped them individually.
Next, we'll add the transformations for the remaining columns. For our example, we'll transform all the remaining columns in one ActiveX script. We start by selecting the remaining columns from the source and destination tables and clicking New to create a New Transformation of type ActiveX Script. Clicking New opens the ActiveX Script Transformation Properties dialog box, which Screen 7 shows. We can implement our custom transformations from this dialog box.
For our example, we'll use VB Script, but we can use any installed ActiveX scripting language, such as JScript or Perl Script. By default, DTS generates a script that maps the selected columns in column order. Again, we have to unmap these transformations and replace them with the correct transformations.
The DTSDestination and DTSSource objects represent the connections that we established to the pubs and HRMart databases. These objects expose the data stream and provide the interface in which we manipulate and transform the columns of data one row at a time. Using a script to transform the remaining columns lets us take advantage of all the functionality a scripting language offers, including the ability to leverage COM objects outside the DTS environment. We can click Parse to parse our script to check whether it's syntactically correct and Test to test the script's logic. The test script writes the data to a temporary file rather than to the destination table.
The Advanced tab in the Data Transformations Properties dialog box lets us increase control over how DTS performs the transformations. For our example, we'll use the default settings.
Next, we'll add a connection to the Northwind database and a Transform Data task that will move the data from Northwind's Employee table to HRMart's Employee table. We follow the same steps as we did with the pubs to HRMart transformations. Listing 2 details the SQL query you can use to extract data from the Northwind database. And Listing 3, page 43, shows the VB Script you can use to define the Northwind data into HRMart data transformations.
Flow Control
We now face the problem of coordinating the data refresh. We want to schedule a refresh of the HRMart data each week. To complete this assignment, we need to add an Execute SQL Task to our package to purge the HRMart Employee table. We can add an Execute SQL Task to the design sheet by dragging it from the Tasks toolbar; right-clicking in the design window, and selecting Add Task; or selecting Tasks, Add Tasks.
Next, we must define the SQL task. The Execute SQL Properties dialog box uses an existing connection to reach its target data. We'll use the previously defined HRMart connection. We can use any valid SQL statement, including stored procedures, in the Execute SQL Properties SQL statement window. We'll use a simple DELETE FROM Employee SQL statement to purge the table.
Now, we need to ensure that our tasks execute in the correct order. First, we need to purge the HRMart data. If the purge is successful, we need to parallel process the pubs to HRMart and Northwind to HRMart transformations. DTS lets developers assign precedence constraints to tasks, including On Success, On Failure, and On Completion. By assigning these constraints in different combinations, developers can implement serial, parallel, and conditional processing.
To establish task precedence, select the source step (HRMart Purge SQL task), then select the destination step (the pubs connection). Next, select Workflow, On Success. This action establishes the precedence with the pubs to HRMart transformation task. You use tasks only in workflow; although we graphically select the pubs connection as the destination step, this selection represents the beginning of the pubs to HRMart transformation task.
DTS offers another method to define precedence. To illustrate this alternative, we'll use it to define the HRMart purge and Northwind to HRMart precedence. First, right-click the Northwind to HRMart transformation task, and click Workflow, which opens the Workflow Properties dialog box. In this dialog box, define any precedence constraints necessary for this task. We select the HRMart Purge task as the source task and Success as the precedence constraint. This configuration tells DTS to run the Northwind to HRMart transformation task only after the HRMart Purge task successfully completes. We enabled the two transformation tasks to execute in parallel. You can use the Options tab in the Workflow Properties dialog box to improve control over the operation. For our example, we'll use the default settings.
Next, we need to save the package to our local server, as Screen 8 shows. In addition to standard SQL Server user security, DTS applies security at the package level. We omitted package-level security. Screen 9 shows our completed package.
SQL Server Agent
You can use the SQL Server Agent or the NT Schedule service to schedule the package for periodic execution. We used the following command-line syntax to execute the package (type as one line):
dtsrun /S Acorn /N "HRMart
Refresh" /U sa /P
The dtsrun.exe utility lets you execute, retrieve, delete, and overwrite any DTS package. We supplied the server name (/S), the package name (/N), a SQL Server user ID (/U), and the user ID's password (/P) as parameters.
Wrap-Up
DTS brings a new dimension to data warehousing. We discussed how DTS attacks two troublesome warehousing tasks, data acquisition and data transformation, and demonstrated how easily you can use the DTS Package Designer to script a package. However, we barely touched on the various COM interfaces that DTS exposes for developing customized transformations. We'll explore some of these programmatic solutions in future articles.
End of Article
Prev. page
1
[2]
next page -->
