Another valuable new DTS feature in SQL Server 2000 is the ability to embed parameters in your queries. You specify a question mark (?), representing a parameter, in the query and map the question mark to a global variable. DTS replaces the question mark with the global variable's value at runtime. To map the question mark to the max_lsn global variable, select Parameters and make sure that max_lsn is mapped to Parameter 1. Click OK twice.
Set the DDQ tasks' properties to implement the refresh processes. Before you start implementing the refresh processes, open the Properties dialog box of one of the DDQ tasks to familiarize yourself with the task. You'll find it's similar in several ways to the Transform Data task (aka the Data Pump). DDQ lets you use a source and a destination like the Transform Data task does. It also lets you use an ActiveX transformation that manipulates the source data in the DTSSource recordset before setting the data to the columns in the DTSDestination recordset.
However, the DDQ task adds another important feature. It lets you dynamically determine the type of activity to be performed at the destination based on an evaluation that the ActiveX script performs on the source row, such as checking the contents of the tran_type column in the Prod_log source table. You determine the type of activity by setting the return value of the Main() function to one of the following values: DTSTransformstat_InsertQuery, DTSTransformstat_DeleteQuery, DTSTransformstat_UpdateQuery, or DTSTransformstat_UserQuery. You write parameterized queries in the Queries tab for Insert, Delete, Update, and Select, and DTS invokes the appropriate query based on the return value of the Main() function. You map the values that you set in the DTSDestination recordset to the queries' parameters.
Try It
If the preceding explanation isn't clear yet, don't worry. Things will become clearer as you set the properties of the DDQ tasks a step at a time. Start by selecting the Source tab in the Properties dialog box of the Type1 Refresh DDQ task. Make sure that Source OLTP is selected in the Connection property, then type the following query in the SQL query box:
SELECT *
FROM Prod_log
WHERE lsn <= ?
ORDER BY lsn
As I mentioned, we'll handle all the transactions in the Prod_log table that have an lsn that's less than or equal to the maximum lsn (i.e., all transactions that existed in the Prod_log table when the Dynamic Properties task retrieved the maximum lsn value). Follow the same procedure you used in the Execute SQL task to map the query's parameter to the max_lsn global variable. Moving to the Bindings tab, make sure that Destination DW is selected in the Connection Property and that the Products_type1 dimension table is selected as the destination table. Later, you'll use the same Source and Destination settings in the other two DDQ tasks, except that each will use the appropriate Products_typeN dimension table as the destination table. On the Transformations tab, create an ActiveX transformation according to the mappings that appear in Figure 2.
Now, to create the Main() function that determines which action to take based on the input, type the code that Listing 6 shows in the ActiveX Script Transformation Properties dialog box. The columns package, productname, and productid_app in the destination recordset are set to the values of the package, productname, and productid columns from the source recordset, respectively. For a Type 1 transformation, we're not interested in the rest of the columns. Also, remember that SQL Server automatically generates the surrogate key in the destination dimension table. The values in the destination recordset will be mapped to parameters in the queries you write in the Queries tab.
The code in the Main() function uses a Select Case command to evaluate the value of the tran_type column from the source row and determines which query type to invoke by setting the function's return value. Click OK twice, then click the Queries tab. Use the information in Table 2 to fill in the parameterized queries and map the parameters to the destination recordset's columns in the Query tab.
The Insert query inserts a new row into the Products_type1 table. The Update query overwrites the productname and package values in the row whose productid_app is being handled. The Delete query sets the discontinued value of the product deleted from the OLTP system to 1.
That's it—you're done implementing the refresh process for the Type 1 transformation. Now click OK and open the Type2 Refresh DDQ task's Properties dialog box. Fill in the properties of the Source and Bindings tabs with the same settings you used in the Type1 Refresh DDQ task, except use Products_type2 as the destination table. On the Transformations tab, create an ActiveX transformation according to the mappings that appear in Figure 3.
Note that with the Type 2 transformation, you're also retrieving the log_date, which will be the effective date, and the packagechg value, which determines whether to create a new dimension row. Figure 3 also shows that the namechg column is retrieved, but in this case, let's use a Type 1 handling for the product name. That way, we can always overwrite the previous product name whether it changes or not. You can omit namechg from the source columns selection. Type the code that Listing 7 shows in the ActiveX Script Transformation Properties dialog box to create the Main() function.
Notice that a value of 1 (minus one) is stored in the destination package value if the source product's package didn't change. The reason for this assignment is that the queries on the Queries tab have access only to the destination recordset (which contains the columns that appear in the Products_Type2 table) and not to the source recordset. This trick lets you tell the query parameters that must map to columns in the destination recordset that a package wasn't changed. For an alternative solution, see the sidebar "Dummy Bindings Table."
Prev. page
1
2
3
[4]
5
next page 
