The lookup feature of DTS allows you to make a separate connection during a query and include data from that connection in the destination table.
For example, you have a destination table that must include the postal abbreviation for a customer’s state; however, the data for State in the source table contains the full name (for example, ME versus Maine). A lookup table in a database on a different server contains the name of each state and its postal abbreviation. You can write a transformation such that when the source data for the State column is read, as part of the query, you connect to the lookup table, query it for the postal abbreviation, and write the results to the appropriate column in the destination table.
Lookups are used frequently to validate data against information stored in other tables. The feature is particularly useful when it is not practical to perform a join operation on another table using a distributed query.
An advantage of the lookup feature is that it can be invoked from inside the transformation function. You associate a query and a connection with the lookup. Although you can achieve similar functionality using COM objects, VARIANTs, and global variables, the advantage of a lookup is that it uses an established connection optimized for quick data retrieval.
You can use lookups as part of a Transform Data task or a Data Driven Query Task. Lookup definitions are made on the Advanced and Options tabs of those tasks.
These are the general steps you use to define a lookup query:
Note Because using ActiveX scripts affect execution speed, use lookups carefully when moving large sets of data.