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.

See Also

Using ActiveX Scripts in DTS