Performing Single-key Lookups from an ActiveX Script

The following two samples demonstrate how you can use parameterized SQL statements and Microsoft® ActiveX® Scripts to create single-key database lookups using DTS Designer in SQL Server Enterprise Manager.

Single-key Lookup Example 1

This sample uses au_lname in a query as a key to get new_lname from the author_lookup table and return it to a script transformation for insertion into the destination column au_lname.

1. Using DTS Designer, create a Transform Data task.
2. In the Transform Data Properties dialog box, click the Advanced tab, and then click Lookups.
3. In the Data Transformation Lookups dialog box, click Add and create a lookup named MyLookup using this query, which specifies au_lname as an input parameter that is supplied by the source table during the transformation process:

SELECT new_lname

FROM author_lookup

WHERE (au_lname = ?)

Note You must use a connection for the lookup other than the one used for source and destination.

4. In the Transform Data Properties dialog box, click the Source tab and specify authors as the source table.
5. Click the Destination tab and specify authors2 as the destination table.
6. Click the Column Mapping tab, and create a mapping between source column au_lname 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.

7. Use this ActiveX script:

Function Main()

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

   Main = DTSTransformStat_OK

End Function

Single-key Lookup Example 2

This sample uses au_lname in a query as a key to get new_lname from the source table and returns au_id to a script transformation for insertion into the destination column.

1. Create a lookup named MyLookup using this query:

SELECT au_id

FROM author_lookup

WHERE (au_lname = ?)

2. In the Tranform Data Properties dialog box, click the Column Mapping tab, create a mapping between source au_lname column and destination au_id column using DTS ActiveX Script Transformation.

Note The source column, au_id, does not require a mapping because the lookup uses the source column, au_lname, to retrieve an au_id value from the lookup table to insert into destination au_id column. You can specify DTS Column Copy Transformation for all other columns.

3. Use this ActiveX script:

Function Main()

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

   Main = DTSTransformStat_OK

End Function