The Lookup in SSIS is one of most useful transformation which is used to compare the source data to the existing data from the reference table (or Lookup Table) and find matching ones. For the non-matching rows we can further do some more work.
Case Sensitivity in Lookup Transformation: Lookup in SSIS is Case-Sensitive, Kana-Sensitive, Accent-Sensitive and so on. Lookup Transformation doesn’t matter even your source and destinations databases setting are case-insensitive. So, if your data is not in proper case, it is advisable to convert your lookup table and source data to Uppercase or Lowercase. Please refer SSIS Lookup Transformation Case Sensitivity article for better understanding.
Steps involved in configuring Lookup in SSIS
Double click on the Lookup in SSIS will open the Lookup Transformation Editor to configure it. Within the General page, it provides us the options to configure Cache Mode, Connection Type and Specify how to handle rows with no matching entries options as shown in the below screenshot.
Lookup in SSIS supports only two types of Connection Managers:
- OLE DB Connection Manager: Lookup Transformation uses OLE DB Connection Manager to access the reference table present in the SQL Server, Oracle and DB2. Please refer Lookup Transformation in SSIS article to understand, How to configure Lookup Transformation using the OLE DB Connection Manager.
- Cache Connection Manager: By default Lookup in SSIS uses OLE DB Connection Manager to connect with the reference table. Other sources can be used, but they must be used indirectly via a Cache Transformation. For example, we can use Text file or an Excel file as lookup table using Cache Connection Manager. Please refer SSIS Lookup Transformation in Full Cache Mode article to understand, How to configure Lookup Transformation using the Cache Connection Manager
You have three options for cache mode:
- Full Cache mode: This is the most commonly used approach in Lookup Transformation. If we selected this option then, entire lookup (or reference) table will be preloaded into the cache (Memory) and Lookup Transformation will perform lookup from the Memory instead of Dataset. This works well when we have less number of rows in the lookup table.
- Partial Cache mode: If we selected this option then, Lookup Transformation starts the transformation with empty cache. When a new row comes from the data flow, the Lookup Transformation first checks for the matching values in its cache. If it is not found in the cache then it will check in the lookup table. And if no match is found, it queries the lookup table. If the match is found in the lookup table then, the value will be cached (stored in the memory) for the next time. If the lookup table is very big then we can use this approach.
- No Cache mode: If we selected this option then, Lookup Transformation will not use cache to store the lookup table at any stage. When a new row comes from the data flow, the Lookup Transformation directly check in the lookup table for the matching values.
The final option in this page is to specify, How to handle rows with no matching entries. Lookup Transformation provides you four options:
- Fail Component (Default): If we selected this option then, When a new row is passed from the data, flow Lookup Transformation will fail the package if it doesn’t found any matching row in the lookup table.
- Ignore Failure: If we selected this option then, When a new row is passed from the data, flow Lookup Transformation will continue its processing even though it doesn’t found any matching row in the lookup table.
- Redirect Rows to No Match Output: If we selected this option then, Lookup Transformation will direct the rows that doesn’t found any matching row in the lookup table to No Match In real-time we mostly use this option.
- Redirect Rows to Error Output: If we selected this option then, Lookup Transformation will direct the rows that doesn’t found any matching row in the lookup table to standard error output.
TIP: Within the Advanced page we adjust the caching properties of the Lookup Transformation.
Thank you for Visiting Our Blog