Incremental Load is nothing but comparing the target table against the source data based on Id or Date or Time Stamp.
Ø If there are any new records in Source data then we have to insert those records into target table.
Ø If there are any updated values in Source data then we have to update those records in the target table.
There are multiple ways to incremental loading in SSIS.
Ø Lookup Transformation
Ø Using CDC (Change Data Capture)
Ø Using SCD (Slowly changing dimension)
Ø Using SQL task (Marge statement )
Here I am using SQL task for the incremental load using Marge statement
When source and destination both are the SQL server then better to use Marge statement for the Incremental load.
See the source and destination in SQL server
I have two tables both are in different database.
|
SELECT * FROM [Test].[dbo].[Books]
SELECT * FROM [TestDB].[dbo].[Books_Destination]
|
Here I am using [Test].[dbo].[Books] as source and [TestDB].[dbo].[Books_Destination] as Destination.
Now I am creating a Sp where I am using Marge statement.
|
CREATE PROCEDURE IncrementalLoadExample
AS
BEGIN
Merge INTO [Books_Destination] As T
using [Test].[dbo].[Books] As S on T.BookID=S.BookID
When MATCHED Then
update set
T.[BookTitle] =S.[BookTitle]
When NOT MATCHED By Target then
Insert
([BookID],[BookTitle])
VALUES
(S.[BookID] ,S.[BookTitle]);
END
|
SP is created successfully.
Now I am coming to SSDT taking SQL script task.
Now I am configuring the SQL Task.
Configure the Connection.
In SQL Statement we are calling EXEC IncrementalLoadExample
Click ok.
The package is ready to run.
Before running the package. See the table in the database.
There is no data in the destination table. Now I am running this package.
See the database
Now I am inserting some record and updating some records in Source.
|
insert into [Test].[dbo].[Books] values(104,'SSRS')
insert into [Test].[dbo].[Books] values(105,'SSAS')
Update [Test].[dbo].[Books] set BookTitle='SSIS Updated'
where BookID='102'
|
Inserting 2 records and updating one record.
Now I am running this package.
See the value in database
