Using Lookup Queries to Modify Data
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.
UpdateEmployee query updates a value in the Employee table when you provide it an
UPDATE Employee SET HasTakenVacation = 0 WHERE EmployeeID = ?
Execute this query for every row in the source rowset with the following Microsoft® ActiveX® script code:
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.