The Lookup in SSIS is one of the most useful transformations 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. Lookup Transformation doesn’t matter even if your source and destinations databases setting are case-insensitive. So, if your SSIS data is not in the proper case, it is advisable to convert your lookup table and source data to Uppercase or Lowercase. Please refer to the Transformation Case Sensitivity article for better understanding.
Steps involved in configuring Lookup in SSIS
Double click on the Lookup in SSIS will open the Transformation Editor to configure it. Within the General page, it provides us with the options to set Cache Mode, Connection Type, and Specify how to handle rows with no matching entries options.
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 to Lookup Transformation article to understand, How to configure Lookup Transformation using the OLE DB Connection Manager.
- Cache Connection Manager: By default, Lookup in SSIS uses the OLE DB Connection Manager to connect with the reference table. Other sources can use, but they must be used indirectly via a Cache Transformation. For example, we can use a Text file or an Excel file as a lookup table using the Cache Connection Manager. Please refer Transformation in Full Cache Mode article to understand, How to configure Lookup Transformation using the Cache Connection Manager
Lookup in SSIS Cache Mode
You have three options for SSIS Lookup cache mode:
- Full Cache mode: This is the most commonly used approach in Lookup Transformation. If we selected this option, the entire lookup (or reference) table would preload into the cache (Memory). And SSIS Lookup Transformation will perform a lookup from the Memory instead of Dataset. It works well when we have less number of rows in the lookup table.
- Partial Cache mode: If we selected this option, SSIS Lookup Transformation starts the transformation with an 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 not found in the cache, it will check in the lookup table. And if no match found, it queries the lookup table. If the match located in the lookup table, the value cached (stored in the memory) for the next time. If the lookup table is massive, we can use this approach.
- No Cache mode: If we selected this option then, Lookup Transformation will not use the cache to store the lookup table at any stage. When a new row comes from the data flow, the SSIS Lookup directly checks in the lookup table for matching values.
Error Handling in SSIS Lookup
The final option on this page is to specify, How to handle rows with no matching entries. The SSIS Lookup Transformation provides you four options:
- Fail Component (Default): When a new row passed from the data flow to lookup. SSIS Lookup will fail the package if it doesn’t find any matching row in the lookup table.
- Ignore Failure: If we selected this option, When a new row passed from the data flow, Lookup Transformation would continue its processing even though it doesn’t find 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 don’t find 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, SSIS Lookup Transformation will direct the rows that don’t find any matching row in the look up table to the standard error output.
TIP: Within the Advanced page, we adjust the caching properties of the Lookup Transformation.