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
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
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.