Managing Zero or Multiple Result Rows in Lookup Queries
When you run a lookup query, Data Transformation Services (DTS) always returns the first row in the result set. Although you are not given access to succeeding rows, you can find out how many rows were returned. This can be useful when a query returns zero or multiple rows.
Result Sets With Zero Rows
Lookup queries sometimes fail to return any rows. For example, if you are tracking the number of vacation days your employees have taken, you might find that some have not taken any vacation days.
When a lookup query retrieves zero rows, DTS returns an empty variant. In Microsoft® ActiveX® code, you can test for this condition with the IsEmpty() function.
For example, the
VacationDays query returns zero rows if the given employee has taken zero vacation days:
SELECT EmployeeID FROM VacationDay WHERE EmployeeID = ?
Using this query, you can skip over employees who have not taken any vacation days by using the following ActiveX script code:
Dim LookupResults LookupResults = DTSLookups("VacationDays").Execute(DTSSource("EmployeeID")) If IsEmpty(LookupResults) Then Main = DTSTransformStat_SkipRow Else Main = DTSTransformStat_OK End If
Note If all data resides on a computer running an instance of Microsoft SQL Server™, performance can be improved by using a source query, instead of a lookup query, to filter out unwanted rows.
Result Sets With Multiple Rows
Lookup queries sometimes return many rows. For example, an employee may have taken many vacation days.
When a lookup query retrieves multiple rows, DTS discards all but the first row. If this is acceptable, you can use the ORDER BY phrase in your query to bring the most important row to the top of the results. In any case, the number of rows returned is accessible through the LastRowCount property of the lookup query.
For example, suppose you want to prepare a vacation day summary. The
RecentVacationDays query lists vacation days for one employee with the most recent vacation day in the first row:
SELECT VacationDate FROM VacationDay WHERE EmployeeID = ? ORDER BY VacationDate DESC
Then, the most recent vacation day date and the total number of vacation days per employee are accessed with the following ActiveX script code:
DTSDestination("LastVacationDate") = DTSLookups("RecentVacationDays").Execute(DTSSource("EmployeeID")) DTSDestination("NumberOfVacationDays") = DTSLookups("RecentVacationDays").LastRowCount
If an employee has taken zero vacation days, the above code nulls the
LastVacationDate and sets
NumberOfVacationDays to zero.