Performing a Multiple-key Lookup from an ActiveX Script

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.

1. Create a lookup named MyLookup using this query:

SELECT new_lname

FROM author_lookup

WHERE au_lname = ? AND au_fname = ?

2. In the Transform Data Properties dialog box, click the Column Mapping tab, and create a 2-to-1 mapping between source columns au_lname, au_fname and destination column au_lname specifying DTS ActiveX Script Transformation.

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.

3. Use this ActiveX script:

Function Main()

   DTSDestination("au_lname") = DTSLookups("myLookup").Execute(DTSSource("au_lname").Value, DTSSource("au_fname").Value )

   Main = DTSTransformStat_OK

End Function