In this tutorial I will explain and give a simple example of SSIS lookup transformation.
Let's start with short explanation of the term lookup. Lookup takes input value and searches for a row in table that contains this value in the specified field; once it finds it you are able to extract values from different fields that belong to the same "row".
Below is a simple example. I have a country table which contains field ID and country (right side). My input value is country with value UK and I want to get ID for this country.
Lookup takes UK then searches Country Field for the entry and when it finds it it returns the ID which 3.
Now that we covered basics let's explain when you would use lookup. There are multiple scenarios however I will limit myself to explain one. Because you use SSIS you most likely are involved in building a data warehouse. During your extract you get source system key (business key) that you want to find in your dimension table and replace with ID (surrogate key) which will be used to load your Fact Table.
NOTE: This example is a simple lookup example using dimension table that does not track history so it should be used only for training purposes and specific scenarios. I will try to write more articles covering typical data warehouse lookups soon.
In this section I will show you how to configure SSIS Lookup transformation (if you are new to SSIS check How to create SSIS package)
Create a new package add data flow in source control. Go to data flow and add source item; I will use OLE DB Source extracts Country field and I will use this field as input for lookup. See below screenshot of data flow and sample of data.
Now that we have source and lookup transformation with green arrow (data paht) we can double click lookup and configure it.
SSIS Lookup General section
Cache mode is set to Full cache which is means that SSIS will take all data from database and store it locally, without checking if data changed. This gives best performance (I will write article about the rest of option soon and provide a link).
Connection Type is OLE DB connection manager which is actually I have ever used.
Type of lookup. At the bottom there is a drop down box with 'Fail Component' value specified. That means that if we don't find a match that lookup will fail and this is what we will use in our case. (again I will try to write something soon about the rest of the options).
Let's move on to connection section.
Specify your connection manager which is the database that contains both field we lookup on and the field we want to get (see preview of data on the right side)
We specify table and in my case it is DimCountry and I clicked preview button so you can see values that my dimension table contains.
Let's move to columns section that is where we will do all the "hard" work.
We can see two tables. The left side is input (comes from source item in data flow) and the right side is what we lookup against which we specified in previous step (connecton section).
Now we need to drag country input field intput lookup country field which is the main part responsible for finding the correct row. After that black line will appear (see screenshot).
I have also clicked ID field on the right side and it appeared below the tables and I renamed Output alias from ID to CountryID. This is another important step that means that new column CountryID will be available in your data flow after the lookup transformation.
Click OK to save the configuration settings.
WARNING: Lookup match is case sensititve so UK is different to uk. Often if case can change (usually free text fields) then use lower() function on both sides. We used table in lookup you will have to change it to sql in order to use lower() function.
Now we need to connect our look to another data flow item. In my case I will use OLE DB destination.
Click lookup item and drag and drop green arrow to your next data flow item which in my case is OLE DB destination. You will get a pop up where you should select "Lookup Match Output" in the first drop down box. The second drop down box should be filled in.
NOTE: Output drop down box also contains "Lookup no match output" but this will not work because we selected "fail component" in general section of lookup transformation. If you want ot have two destination. One for match and another one for no match you will have to change setting in general section (to redirect no match).
Now we just have to run the package. Below you can see previes of data. You can see our input column Country and CountryID that we got after performing the lookup.
I hope that helps.
I will try to add more SSIS lookup transformation articles soon.