The following sample demonstrates how you can use a parameterized SQL statement and a Microsoft® ActiveX® Script to create a multiple-key database lookup using DTS Designer in SQL Server Enterprise Manager. The query requires both au_lname and au_fname as input parameters from the source table during transformation and returns new_lname from the lookup table for insertion into the destination au_lname column.
SELECT new_lname
FROM author_lookup
WHERE au_lname = ? AND au_fname = ?
Note You only need to specify DTS ActiveX Script Transformation for those columns that are used in the lookup. You can specify DTS Column Copy Transformation for the other columns.
Function Main()
DTSDestination("au_lname") = DTSLookups("myLookup").Execute(DTSSource("au_lname").Value, DTSSource("au_fname").Value )
Main = DTSTransformStat_OK
End Function