An overview of the LOOKUP TRANSFORMATION in SSIS
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 data is not in the proper case, it is advisable to convert your lookup table and source data to Uppercase or Lowercase.
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 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.
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.
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 SSIS 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.
I have a .csv file with 30 rows and Look-up table in SQL server. The look-up table is containing two extra columns City and Country I want to add to the matching rows for .csv table - source table. I have to join these tables together. This can be done based on the:
1. ID columns or
2. FirstName & LastName columns
because the values are identical in both tables. As a result I will get back the data from .csv (source table) plus City and Country as a new columns from the look up table in SQL server. These data will be loaded into MatchedRecords table.
Lookup transformation works like a Left Join in SQL server. The final result I will get back will look like this. I am going to do a Lookup transformation based on the Id columns and additionally I want to retrieve columns City and Country from .csv file back.
Add the source table - .csv file
Add Lookup transformation, Redirect rows form no match output.
Step 3: Connection
Specify your look-up table.
Step 4: Columns
Join these two tables with the Id columns. I could use FistName and LastName because these columns contain distinct values. This transformation will return the matching rows from the .csv table plus I will retrieve City and Country columns from SQL table and then I load the matching rows back to the SQL Server.
Click OK to finish this part of transformation
Add OLE DB Destination for Lookup Match Output. Means I will load the matching rows to the server and I do not need the non matching rows. I use Multicast for non matching. You do not have to connect them to nothing, you can just ignore this output.
Set up destination. Matched rows will go to a new table.
Step 7: Run the package