Data Transformation Services
Enhancing Lookup Queries in DTS
When implementing Data Transformation Services (DTS) lookup queries, consider the following:
- DTS lookups are best used when the input to the lookup is an external value (for example, a global variable).
- Lookups can be invoked from within a transformation function, allowing you to associate a query and a connection with the lookup. In a transformation function, you can then execute the query and have one or more values returned. You can achieve similar functionality using COM objects, VARIANTs, and the DTS GlobalVariables collection. However, the Lookup object uses an established connection and is optimized for quick data retrieval using caching.
If the transformation function can use an SQL statement instead of a lookup (for example, a SELECT statement with a join clause), performance can be greatly improved.
- You can configure a lookup query to cache results by specifying a cache size. If the cache size is larger than zero, then the results of the query are cached along with parameter values. If you provide the same parameter values to the query again, the results are returned from the cache with no additional database access. When the cache fills up, rows are removed in least recently used order.
- The lookup query connection must be able to support the OLE DB ICommand interface in order to accept SQL statements directly. Due to this restriction, connections such as text files cannot be used for lookups.
- If a task joins the package transaction and the lookup connection supports transactions, any updates made by lookup queries also take part in the package transaction. If the task joins the package transaction and the lookup connection does not support transactions, on rollback, updates made by lookups will remain on file.
A lookup query may fail (for example, if it attempts to insert a row with a duplicate key). If a lookup fails, no further processing takes place for the source row. Lookup failure counts as one error against the maximum errors property of the containing task.