Incremental data load in SSIS through Lookup Transformation | Part 13

Incremental data load in SSIS through Lookup Transformation:
Requirement:
1) SQL Server Data Tools for Visual Studio 2015
or
SQL Server Data Tools or Visual Studio 2019
Steps need to follow:
1) Add data flow task in designer and double click on it.
2) Add data source OLEDB and destination OLEDB and configure it both with connection string.
3) And run the projects.
Incremental Load: comparing the target table (Destination data) against the source data based on Id or Date Stamp or Time Stamp.
• if new records in Source data, then we have to insert those records in the target table.
• If any updated values in Source data, then we have to update those records in the target table.
Types of incremental load:
i) Lookup Transformation
ii) Using CDC (Change Data Capture)
iii) Using SCD (Slowly changing dimension)
iv) Using SQL task (Merge statement)
Let us see here look up transformation, how can we use it to transfer the incremental data.
Lookup Transformation:
It checks each record of the table in the source database against the lookup (target) table in the destination database.
If a record is not found, it will be added to the target table.
If a record is found, the data of the record in the target table will be replaced with that of the source table.
Let us start:
Server name: your database ip
Source Table:
CREATE TABLE [tbl_emp_detail](
[empid] [int] NOT NULL,
[emp_name] [nvarchar](255) NULL,
[emp_location] [nvarchar](255) NULL,
[salary] [float] NULL,
CONSTRAINT [pk_empid_1] PRIMARY KEY CLUSTERED
(
[empid] ASC
)
)
insert into [tbl_emp_detail](empid,emp_name,emp_location,salary)
values(106, 'Rajan','Mumbai',50000)
insert into [tbl_emp_detail](empid,emp_name,emp_location,salary)
values(106, 'Ramesh','Delhi',4000)
Destination table: tbl_emp_detail_destination