Use of Lookup Transformation in SQL Server Integration Services

This article describes the use of a Lookup Transformation in SQL Server Integration Services. A Lookup Transformation is very useful when we need to update data frequently.

First of all create 2 tables called TempRanks and Ranks and a Stored Procedure in SQL as in the following:

  1. CREATE TABLE [dbo].[TempRanks](  
  2.       [RankId] [bigint] IDENTITY(1,1)NOT NULL,  
  3.       [UserId] [varchar](50)NULL,  
  4.       [TotalPoint] [bigintNULL,  
  5.       [Rank] [bigintNULL,  
  6.  CONSTRAINT [PK_TempRanks]PRIMARY KEYCLUSTERED  
  7. (  
  8.       [RankId]ASC  
  9. )WITH(PAD_INDEX = OFF,STATISTICS_NORECOMPUTE = OFF,IGNORE_DUP_KEY =OFF,ALLOW_ROW_LOCKS  =ON,ALLOW_PAGE_LOCKS  =ON)ON [PRIMARY]  
  10. )ON [PRIMARY]  
  11. GO  
  12. CREATE TABLE [dbo].[Ranks](  
  13.       [RankId] [bigintNULL,  
  14.       [UserId] [varchar](50)NULL,  
  15.       [TotalPoint] [bigintNULL,  
  16.       [Rank] [bigintNULL  
  17. )ON [PRIMARY]  
  18. GO  
Insert records into TempRanks table.
  1. CREATE PROCEDURE [dbo].[P_CalculateStudentMarks]     
  2. (     
  3. @UserID varchar(50),     
  4. @RankId bigint,     
  5. @TotalPoint bigint,     
  6. @Rank bigint     
  7. )     
  8. AS     
  9. BEGIN     
  10. SET NOCOUNT ON;     
  11. Update Ranks      
  12.  Set RankId = @RankID,      
  13.   TotalPoint = @TotalPoint,      
  14.   [Rank] = @Rank     
  15.  WHERE UserId = @UserID     
  16. SET NOCOUNT OFF;     
  17. END  

Open Visual Studio 2008 and create a new project.

Image1.png

Drag and drop Data Flow Task from Control Flow Items.

Image2.png
 

Right-click on connection managers to create a new OLE DB connection.

Image3.png

Image5.png

If the connection is successfully created then the following screen will display:

Image6.png

Right-click on Data Flow Task and then click on Edit.

Image7.png

Now, Drag and drop OLE DB Source from Data Flow Sources.

Image8.png

Drag and drop Lookup Transformation from Data Flow Transformations in SSIS Toolbox.

Image9.png

Right-click on Lookup to select the table.

Image10.png

Join OLE DB Source and Lookup with arrow green arrow sign.

Image11.png
 

Again right-click on Lookup and Edit.

Image12.png

Click on columns and see the available input columns and available lookup columns.

Image15.png

Now Drag and drop OLE DB Destination from Data Flow Destinations.

Image16.png
 

Join Lookup Transformation and OLE DB Destination with green arrow.

Image17.png

A new popup will display like this:

Image18.png

Click on "OK". The new screen will look like this and select the required table:

Image19.png

Also see the mappings.

Image20.png

Drag and drop OLE DB Command.

Image21.png

Join Lookup Transformation and OLE DB Command with the green arrow.

Change connection manager settings.

Image22.png

Click on component properties and in SQLCommand write the syntax like:

  1. EXEC dbo. P_CalculateStudentMarks ?,?,?,?  

Image23.png

Click on column mappings and map the columns as per input parameter.

Image24.png
 

Build the project.

Right-click on Package.dtsx to execute the Package. The first time it will insert all records from the TempRanks table into the Ranks table. Again we execute the package, only new records will be inserted into Ranks table from the TempRanks table.

And the existing records will be updated of the Ranks table.