--- title: "Implement a Lookup in No Cache or Partial Cache Mode | Microsoft Docs" ms.custom: "" ms.date: "03/01/2017" ms.prod: sql ms.prod_service: "integration-services" ms.reviewer: "" ms.technology: integration-services ms.topic: conceptual helpviewer_keywords: - "Lookup transformation" - "match exactly [Integration Services]" - "lookups [Integration Services]" - "exact matches [Integration Services]" ms.assetid: 01b7fbca-5181-4d47-9f75-7f25af6b40d2 author: chugugrace ms.author: chugu --- # Implement a Lookup in No Cache or Partial Cache Mode [!INCLUDE[ssis-appliesto](../../../includes/ssis-appliesto-ssvrpluslinux-asdb-asdw-xxx.md)] 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 [!INCLUDE[ssBIDevStudioFull](../../../includes/ssbidevstudiofull-md.md)], open the [!INCLUDE[ssISnoversion](../../../includes/ssisnoversion-md.md)] 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](../../../integration-services/connection-manager/ole-db-connection-manager.md). 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 an 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 an 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](../../../integration-services/data-flow/integration-services-data-types.md). 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)](../../../integration-services/data-flow/transformations/lookup-transformation-editor-error-output-page.md). 14. Click **OK** to save your changes to the Lookup transformation, and then run the package. ## See Also [Integration Services Transformations](../../../integration-services/data-flow/transformations/integration-services-transformations.md)