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 the proper case, it is advisable to convert your lookup table and source data to Uppercase or Lowercase. Please refer 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 with 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 a Text file or an Excel file as a lookup table using the Cache Connection Manager. Please refer Lookup 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 then, entire lookup (or reference) table will be preloaded into the cache (Memory). And Lookup Transformation will perform a 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 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 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 SSIS Lookup directly checks in the lookup table for matching values.
Error Handling in SSIS Lookup
The final option in this page is to specify, How to handle rows with no matching entries. SSIS Lookup Transformation provides you four options:
- Fail Component (Default): When a new row is 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 then, When a new row is passed from the data flow Lookup Transformation will 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, Lookup Transformation will direct the rows that don’t find any matching row in the lookup table to the standard error output.
TIP: Within the Advanced page we adjust the caching properties of the Lookup Transformation.
Thank you for Visiting Our Blog