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.

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.

See Also

Using ActiveX Scripts in DTS