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, and 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 a better understanding.
Steps involved in configuring Lookup in SSIS
Double-click on the Lookup will open the Transformation Editor to configure it. The General page provides us with the options to set Cache Mode and 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 the Lookup Transformation article to understand, How to configure Lookup Transformation using the OLE DB Connection Manager.
- Cache Connection Manager: By default, Lookup 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, using the Cache Connection Manager, we can use a Text file or an Excel file as a lookup table. 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. The entire lookup (or reference) table would preload into the cache (Memory) if we selected this option. And Transformation will perform a lookup from the Memory instead of the Dataset. It works well when we have less number of rows in the lookup table.
- Partial Cache mode: SSIS Lookup Transformation starts the transformation with an empty cache if we select this option. 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, it will check in the lookup table. And if no match is found, it queries the lookup table. If the match is located in the table, the value is 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 select 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, it 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 with four options:
- Fail Component (Default): When a new row passes from the data flow to lookup. It 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 select 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 select this option, 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.
Comments are closed.