Lookup Transformation performs lookups by joining data in input columns with column in a referenced data set. Lookup Transformation are very useful in SQL Server Integrations Services ETL process in Data warehouse builds where we load data from various data source systems. Lookup transformations is a synchronous transformation. This means it does not block the data flow pipe line. Image: Lookup 1.0

Note: Lookup Transformation is case sensitive, so for any string match we need to covert the dataset to either lower or upper case in source SQL using UPPER() or LOWER() T-SQL functions or we could use character map transformation to convert the dataset to upper or lower case. Cache Mode: Lookup Transformations have below three cache mode options, which allows us to specify how referenced data set is stored in memory. Image: Lookup 2.0 Full cache: It is a default cache mode. In this mode data will be queried to database during the pre-execute phase of the data flow task. This mode used most memory and takes additional start up time for the data flow task, and all of the caching happens before any rows retrieved from the data flow source but it will be very fast during execution. One thing to remember that it will not swap memory to disk until the process (package execution) finished so it is important to manage memory resource. I have seen many people set full table/view name (i.e. do not use SELECT Column1 , Column2 From [TableName], Required Columns Only.) with 50 columns or more with more than 10 million rows which consume large amounts of memory about 5-8 GB RAM instead of required columns which consume hundreds of MB. Partial cache: In this cache mode Lookup cache is empty at the beginning of the data flow. When a new row comes in, the Lookup Transformation checks its cache for the matching values. If no match is found it queries the database. If match is found the values are cached they can be used the next time a matching rows comes in. No Cache: This cache mode is only keeps last match row in to memory, it means that in this mode Lookup Transformation query database for each row. Connection Type: We will have below to connection type options. Image: Lookup 3.0 Cache connection manager: Cache connection manager is useful to reduce data base load while using reuse cache. It is also useful to reuse cache between different data flow task and Lookup Transformations to manage memory more efficiently. We can reuse cache for role playing dimensions such as Date, time and other role playing dimensions in your data warehouse. We must add Lookup transformation in separate data flow task, this will allow cache transformation to populate Cache connection manager before the Lookup Transformation execution starts. The advantage of Cache connection manager is we can do Lookup Transformation against non OLE DB data sources. Note: Cache connection manager does not support Binary Large Object (BLOB) data types DT_TEXT, DT_NTEXT, and DT_IMAGE. If reference dataset contains a BLOB data type, the component will fail when we run the package. OLE DB connection manager: We can use OLE DB Connection manager for OLE DB data sources. Specify how to handle rows with no matching entries. This allows options for handling rows that do not match at least one entry in the dataset. Image: Lookup 4.0 Fail Component: This is default selected option. Package fails if any of the rows failed to match with referenced data set or raw caused error, for example data truncation. Note: People leave this option in production SSIS Package which is not idle because when we schedule a package for an overnight run and early morning we found schedule job failed. It is better to set default key for the failed row match and report in the morning or Redirect Rows to Error Output with some form of the reporting. Ignore Failure: This option sends any nonmatching rows and any rows that cause errors (such as truncation error) to the same output as matched rows, but any lookup values will be set to null. It is like a LEFT OUTER JOIN in SQL query but not returning any duplicate rows. Redirect Rows to Error Output: This option sends any nonmatching rows and any rows that cause an error to the error output (output with red arrow). Redirect Rows to no Match Output: This option sends any nonmatching rows output to the no match output and error rows to the error output (output with red arrow). We can load data to the Fact table load with below two options. We will try and compare which option is faster, uses less resources and required less development and maintenance efforts. I am not going to go through each steps, but through the below print screens I wanted to prove an effective way to use Lookup transformations in fact table load which has about 15-20 dimensions or more. Option 1: In Image Lookup 5.0 we can see that I have redirected the Lookup transformation LKP_OrganizationKey’s no match output “Nonmatched OrganizationKey” to “DER_OrganzationKey” Derived Column transformation and match output to “UnionALL_OrganizationKey” Union All transformation. Same as, LKP_AccountKey Lookup Transformation’s non matched output “NonMatched AccountKey” to DER_AccountKey Derived Column transformation and matched output “Matched Accountkey” to DER_AccountKey Derived column transformation. Many people uses option 1 with lookup transformation, if match is not found set default value in Derived column transformation and merge match output with Union ALL Transformation. Image: Lookup 5.0 Option 2: In Image Lookup 6.0 I have selected, ignore failure option to specify how to handle rows with no matching entries. So all the match output, no match output rows with NULL Lookup column results and any error rows are redirected to the same match output pipeline for LKP_OrganizationKey and LKP_AccountKey Lookup transformations. Then in DER_DefaultKey derived column transformation I have added expression in Derived column transformation editor as per Image Lookup 7.0. Image: Lookup 6.0 Image: Lookup 7.0 Conclusion: As we have seen that Lookup transf-ormation is non-blocking transformation but if we use it with Union ALL transformation (Option1) which is partial blocking transformation so the whole transformation becomes partial blocking transformations. Also, if we have more than 15-20 dimension then we will have to spend more time in development. Option 2 is much faster because and less resource intensive because Lookup Transformation and Derived Column Transformation both are non-blocking transformation. Also, option 2 is best for best practices.