Partilhar via

Lookup Transformation Editor (Advanced Tab)

  • Artigo

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

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.