Lookup Transformation Editor (Advanced Tab)

Use the Advanced tab of the Lookup Transformation Editor dialog box to configure caching for the Lookup transformation.

By default, the Lookup transformation caches the entire reference table in an in-memory cache for best performance. If you do not select any options on the Advanced tab, the Lookup transformation uses this default behavior. The options on the Advanced tab allow you to configure partial caching, if there are memory limitations.

Integration Services and SQL Server differ in the way they compare strings. If the Lookup transformation is configured to use the default cache type, in which the entire reference table is cached, Integration Services does the lookup comparison in the cache; otherwise, the lookup operation uses a parameterized SQL statement and SQL Server does the lookup comparison. This means that the Lookup transformation may return a different number of matches from the same lookup table depending on the cache type.

To learn more about the Lookup transformation, see Lookup Transformation.

Options

• Enable memory restriction
  Enable partial caching, in which the Lookup transformation specifies the size of the cache that is loaded with reference data. This option is available only with connections that support keyed access.

  To enable partial caching, you must also select Enable caching, Modify the SQL statement, or both options. Otherwise, partial caching is disabled.

• Enable caching
  Enable the specified cache size.

• Cache size
  If you enable caching, adjust the in-memory cache size (in megabytes). The default value is 5 megabytes.

• Modify the SQL statement
  Modify the SQL statement used to populate the lookup cache in the Caching SQL statement text box.

• Caching SQL statement
  View and edit the SQL statement used to populate the in-memory lookup cache.

• Parameters
  Map columns to input parameters by using the Set Query Parameters dialog box.

• Configure Error Output
  Specify how the component should handle row-level errors by using the Configure Error Output dialog box.

See Also

Reference

Lookup Transformation Editor (Reference Table Tab)
Lookup Transformation Editor (Columns Tab)

Other Resources

Integration Services Error and Message Reference
Fuzzy Lookup Transformation
How to: Implement a Lookup Using the Lookup Transformation

Help and Information

Getting SQL Server 2005 Assistance

Change History