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:

CREATE TABLE [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
CREATE TABLE [dbo].[Ranks](
      [RankId] [bigint] NULL,
      [UserId] [varchar](50)NULL,
      [TotalPoint] [bigint] NULL,
      [Rank] [bigint] NULL
)ON [PRIMARY]
GO

Insert records into TempRanks table.

CREATE PROCEDURE [dbo].[P_CalculateStudentMarks]
(
@UserID varchar(50),
@RankId bigint,
@TotalPoint bigint,
@Rank bigint
)
AS
BEGIN
SET NOCOUNT ON;
Update Ranks
 Set RankId = @RankID,
  TotalPoint = @TotalPoint,
  [Rank] = @Rank
 WHERE UserId = @UserID
SET NOCOUNT OFF;
END

Open Visual Studio 2008 and create a new project.

Drag and drop Data Flow Task from Control Flow Items.

 

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

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

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

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

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

Right-click on Lookup to select the table.

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

 

Again right-click on Lookup and Edit.

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

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

 

Join Lookup Transformation and OLE DB Destination with green arrow.

A new popup will display like this:

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

Also see the mappings.

Drag and drop OLE DB Command.

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

Change connection manager settings.

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

EXEC dbo. P_CalculateStudentMarks ?,?,?,?

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

 

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.