Floating FB popout byF5debug

SQL Server Integration Services (SSIS) – Part 48 – Lookup Transformation in SSIS


This is part 48 of the series of article on SSIS


In this article we are going to see on how to use 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. 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 below data sources only SQL, Oracle and DB2. 


Let’s jump start on how to use this task in real time and see the steps to do the configurations. You can look into my series of article on SSIS at the url - http://f5debug.net/tutorial/ssis.aspx


Follow steps 1 to 3 on my first article to open the BIDS project and select the right project to work on integration services project. Once the project is created, we will see on 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 some other destination table for time being shown an output as shown below



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

About Author: Karthikeyan Anbarasan, Microsoft MVP (Most Valuable Professional) in ASP.NET/IIS Architecture. He is the Founder and Chief Editor of the www.f5debug.net website and has authored books on Windows Phone and Business Intelligence(SSIS). He is also a Passionate Speaker and a Blogger on Microsoft Technologies.

You can Join Me On: Facebook, Twitter, Google Plus, LinkedIn

Add comment

  Country flag

  • Comment
  • Preview

Recent Posts

Random Posts

Most Viewed

URL Shortner

Create your own short urls!!!

Site Counter

free counters


This is a personal weblog. The opinions expressed here represent my own and not those of my employer. For accuracy and official reference refer to MSDN/ TechNet. I have documented my personal experience on this blog.

Protected by Copyscape Web Plagiarism Finder

Site Meter