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:

 CREATETABLE [dbo].[TempRanks](
      [RankId] [bigint] IDENTITY(1,1)NOT NULL,
      [UserId] [varchar](50)NULL,
      [TotalPoint] [bigint] NULL,
      [Rank] [bigint] NULL,
 CONSTRAINT [PK_TempRanks]PRIMARY KEYCLUSTERED
(
      [RankId]ASC
)WITH(PAD_INDEX = OFF,STATISTICS_NORECOMPUTE = OFF,IGNORE_DUP_KEY =OFF,ALLOW_ROW_LOCKS  =ON,ALLOW_PAGE_LOCKS  =ON)ON [PRIMARY]
)ON [PRIMARY]
GO
CREATETABLE [dbo].[Ranks](
      [RankId] [bigint] NULL,
      [UserId] [varchar](50)NULL,
      [TotalPoint] [bigint] NULL,
      [Rank] [bigint] NULL
)ON [PRIMARY]
GO
Insert records into TempRanks table.
CREATEPROCEDURE [dbo].[P_CalculateStudentMarks]   
(   
@UserID varchar(50),   
@RankId bigint,   
@TotalPoint bigint,   
@Rank bigint   
)   
AS   
BEGIN   
SETNOCOUNT ON;   
Update Ranks    
 Set RankId = @RankID,    
  TotalPoint = @TotalPoint,    
  [Rank] = @Rank   
 WHERE UserId = @UserID   
SETNOCOUNT OFF;   
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:

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.