Lookup Transformation Editor (Advanced Tab)

Updated: 12 December 2006

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.

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.


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.

Release History

12 December 2006

Change content:
  • Clarified how to enable partial caching.

14 April 2006

New content:
  • Added information about effects of using different cache types.

Community Additions