SSIS lookup transformation is a task of SSIS (Microsoft Integration Services) packages. This is one of the most typical of an ETL process (Extract Transform and Load).
SSIS packages are widely used in Business Intelligence to consume data from ERP systems or unstructured data sources. Their aim is to fill data in the data warehouse. This data layer represents the fondations of data models and reporting solutions.
One of the first tasks in a data warehouse construction is to normalize all the information we have in our sources and build a snowflake schema. The snowflake schema has a fact table in its center. This facts table represents the measures that we want to show and analyze in our reporting.
These measures are linked to the different axis of the analysis, usually called “dimensions” (dimensions of analysis). Each dimension represents a master data of all the possible entries in the facts table.
Little explanation: if we want to create a report showing the use of some materials, our master data will be composed of all the materials.
When creating these dimensions of analysis, we create a table in our database for each dimension. The best practice wants that this dimension table always has a unique key as primary key.
The lookup in a snow flake schema
When we populate the fact table is best practice to lookup for each record that we have in the source. This way we build n-1 connections from the facts table to our dimension table.
Coming back to our materials example. With this lookup we’re practically translating the codes that each material has in an ERP system in a key that will be used in the BI reports. The advantage is that if we have the same material coming from different ERP sources we will the normalize this information decoding all the records that we get to one and only one key.
So the lookup task responds to this need. You can find it in the SSIS Toolbox
When you create a lookup task in Microsoft Integration Services you need to specify “where” you want to lookup for the master data. You can use either a table or SQL query. In the example below I am using a query to lookup for the legal unit key that I have in my DWH.
Then use the Columns tab to decide in which field of the table to look and what to return as a result of the lookup. In my example I am looking in the dim_entity_global_bk field and returning the dim_entity_global_sid