Using Multiple Lookup Queries

Data Transformation Services (DTS) places no limitations on the number of lookup queries that can appear in a single Microsoft® ActiveX® Script transformation. Therefore, you can use multiple lookup queries to:

• Add, change, or delete an account, depending on values in a source transaction table.
  
  
• Carry out an update only after a query returns successfully.
  
  
• Look up dimension table keys, given corresponding values from an online transaction processing (OLTP) system.

Carrying Out an Update

Two queries, VacationDays and UpdateEmployee, can be combined to update only those employees who have taken no vacation days:

Dim LookupResults
LookupResults = DTSLookups("VacationDays").Execute(DTSSource("EmployeeID")) 
If Not IsEmpty(LookupResults) Then
   DTSLookups("UpdateEmployee").Execute(DTSSource("EmployeeID"))
End If

Looking Up Dimension Table Keys

Data warehouse dimension tables are often keyed with identity fields. These fields do not necessarily occur in the OLTP system that provides facts for the data warehouse. For example, in an OLTP system, the product might be keyed by the SKU field, whereas in the data warehouse, it is keyed by the automatically generated ProductID. Before a new SalesFact can be inserted, the SKU field must be used to look up the corresponding value of ProductID in the data warehouse.

In the following example code, the GetProductID query retrieves a ProductID, given an SKU passed in as a parameter:

SELECT ProductID FROM Product WHERE SKU = ?

In the same way, a CustomerID can be retrieved given an account number. Here is the GetCustomerID query:

SELECT CustomerID FROM Customer WHERE AccountNumber = ?

Fill the ProductID and CustomerID columns in the SalesFact table with the following ActiveX script code:

DTSDestination("ProductID") = 
   DTSLookups("GetProductID").Execute(DTSSource("SKU"))
DTSDestination("CustomerID") = 
   DTSLookups("GetCustomerID").Execute(DTSSource("AccountNumber"))

To look up additional values, (for example, the StoreID), add another query and another line of script code.

For more information about queries that appear in this example, see Managing Zero or Multiple Result Rows in Lookup Queries or Using Lookup Queries to Modify Data.