Lookup queries are not limited solely to SELECT statements. INSERT, DELETE, and UPDATE statements, as well as stored procedure invocations, all can appear in lookup queries.
The UpdateEmployee query updates a value in the Employee table when you provide it an EmployeeID:
UPDATE Employee SET HasTakenVacation = 0 WHERE EmployeeID = ?
Execute this query for every row in the source rowset with the following Microsoft® ActiveX® script code:
DTSLookups("UpdateEmployee").Execute(DTSSource("EmployeeID"))
Note An INSERT query can be used in conjunction with a Transform Data task to split a source rowset, sending rows to two different tables. However, when both tables reside in Microsoft SQL Server™ databases, this practice results in longer execution times than sequentially running two Transform Data tasks with fast load enabled.