SQL Server Integration Services (SSIS) - Lookup Transformation in SSIS

Introduction:

In this article we are going to see how to use a Lookup Transformation in SSIS Packaging. Lookup transformations are mainly used to provide a join with some other source with the current source and fetch the result in a much needed format. The joining source can be any one of the following: cached objects, a table, a destination file source, a result from a query etc. Lookup transformations are available for the following data sources only SQL, Oracle and DB2. Let's jump start to how to actually use them and see the steps to do the configurations.
You can look into my series of article on SSIS at the url - http://f5debug.net/all-articles/

Steps:

Follow steps 1 to 3 on my first article to open the BIDS project and select the right project to work on an integration services project. Once the project is created, we will see how to use the Lookup Transformation task. Once you open the project just drag and drop the Lookup control and a source provider as shown in the below image.


You can see the red marks on the control which indicates that the configuration is not done with the controls. Let's do the configuration one by one so that it's easy for the readers to get it done practically.
The scenario which we are going to take and create a package is we have a sample text file which has the customer details we are going to make use of that as source and do a lookup with the database and the finally send it to the destination db.


Now configure the source file with the source provider as shown below:


Now we need to configure the lookup transformation as shown in the below screen. Here we need to select the table where the lookup should happen and select the mapping correctly as shown in the below screen.


Here using the region code only we are going to do the lookup and do the mappings and get the desired result based on the flat file and the source.
Once we are done with the configuration of the source and the lookup transformation we need to specify the destination. Please take care of the mapping as the priority. Here in this example since I don't have the desired table as output I map it to some other destination table for time being shown an output as shown below:

Conclusion:

So in this article we have seen how to use the Lookup transformation task and the key configurations used in order to use this task handy.