Implement a Lookup in No Cache or Partial Cache Mode

  • Article

Applies to: SQL Server SSIS Integration Runtime in Azure Data Factory

You can configure the Lookup transformation to use the partial cache or no cache mode:

  • Partial cache

    The rows with matching entries in the reference dataset and, optionally, the rows without matching entries in the dataset are stored in cache. When the memory size of the cache is exceeded, the Lookup transformation automatically removes the least frequently used rows from the cache.

  • No cache

    No data is loaded into cache.

Whether you select partial cache or no cache, you use an OLE DB connection manager to connect to the reference dataset. The reference dataset is generated by using a table, view, or SQL query during the execution of the Lookup transformation

To implement a Lookup transformation in no cache or partial cache mode

  1. In SQL Server Data Tools (SSDT), open the Integration Services project that contains the package you want, and then open the package.

  2. On the Data Flow tab, add a Lookup transformation.

  3. Connect the Lookup transformation to the data flow by dragging a connector from a source or a previous transformation to the Lookup transformation.

    Note

    A Lookup transformation that is configured to use the no cache mode might not validate if that transformation connects to a flat file that contains an empty date field. Whether the transformation validates depends on whether the connection manager for the flat file has been configured to retain null values. To ensure that the Lookup transformation validates, in the Flat File Source Editor, on the Connection Manager Page, select the Retain null values from the source as null values in the data flow option.

  4. Double-click the source or previous transformation to configure the component.

  5. Double-click the Lookup transformation, and then in the Lookup Transformation Editor, on the General page, select Partialcache or No cache.

  6. For Specify how to handle rows with no matching entries list, select an error handling option from the list.

  7. On the Connection page, select a connection manager from the OLE DB connection manager list or click New to create a new connection manager. For more information, see OLE DB Connection Manager.

  8. Do one of the following steps:

    • Click Use a table or a view, and then either select a table or view, or click New to create a table or view.

    • Click Use results of a SQL query, and then build a query in the SQL Command window.

      -or-

      Click Build Query to build a query by using the graphical tools that the Query Builder provides.

      -or-

      Click Browse to import a SQL statement from a file.

    To validate the SQL query, click Parse Query.

    To view a sample of the data, click Preview.

  9. Click the Columns page, and then drag at least one column from the Available Input Columns list to a column in the Available Lookup Column list.

    Note

    The Lookup transformation automatically maps columns that have the same name and the same data type.

    Note

    Columns must have matching data types to be mapped. For more information, see Integration Services Data Types.

  10. Include lookup columns in the output by doing the following steps:

    1. From the Available Lookup Columns list, select columns.

    2. In Lookup Operation list, specify whether the values from the lookup columns replace values in the input column or are written to a new column.

  11. If you selected Partial cache in step 5, on the Advanced page, set the following cache options:

    • From the Cache size (32-bit) list, select the cache size for 32-bit environments.

    • From the Cache size (64-bit) list, select the cache size for 64-bit environments.

    • To cache the rows without matching entries in the reference, select Enable cache for rows with no matching entries.

    • From the Allocation from cache list, select the percentage of the cache to use to store the rows without matching entries.

  12. To modify the SQL statement that generates the reference dataset, select Modify the SQL statement, and change the SQL statement displayed in the text box.

    If the statement includes parameters, click Parameters to map the parameters to input columns.

    Note

    The optional SQL statement that you specify on this page overrides and replaces the table name that you specified on the Connection page of the Lookup Transformation Editor.

  13. To configure the error output, click the Error Output page and set the error handling options. For more information, see Lookup Transformation Editor (Error Output Page).

  14. Click OK to save your changes to the Lookup transformation, and then run the package.

See Also

Integration Services Transformations