SSIS_Incremental Loading

1.Incremental loading using Lookup Transformation 

In this demo we use EID as the primary key in the source table.

In the first lookup we match the primary key. The NoMatched data is inserted and the matched data is updated.

For updating the data in SSIS OLEDB command task.

Mapping the parameters to the columns.

For Lookup unmatched row we update. We dont update for all the rows. so we are adding one more lookup with all the columns in the lookup transformation. After this we fiter only the Data which is notmatched.

So in the second lookup tranformation(marked abv) we map all the columns.

Post that we only take the No Matched data and update it. 

If you want to add another column in the source table(in sql side you use the below) 

For adding the same Ins_date column in the DWH table we use Derived Column transformation as below.

Hence the full package for Incremental Loading is ready.

2.Incremental Loading using SCD 

Type 0 = Change is treated as error

Type 1 = The row is updated 

Type 2 = It will capture the history of a row

To implement type 2 on a column, we need to remove the Primary key as this will throw errors , instead on Primary key we will include a Surrogate Primary key.

After finishing the task, SCD will create the incremental package by itself.

3. Incremental Loading using Merge Statement.

Merge statement is a combination statement that can perform INSERT, UPDATE, DELETE statements based on whether rows that match the selection criteria exists in the table or not.

MERGE Target_TAble as target

USING Source_Table as Source

On Condititon

When Matched THEN

UPDATE

SET Column = Source.Column,

WHEN TARGET IS NOT MATCHED THEN

INSERT Values ( Source.Column1 , Source.Column2)

Merge DimEmp as D

Using Emp as S

On S.EID = D.EID

--Update

When Matched and ( D.name <> S.name or D.Salary <> S.Salary )

Then 

Update 

Set D.name = s.name , D.Salary = s.salary

--Insert

When Not Matched by Target

Then

Insert ( Eid, Name, Salary) Values (S.eid, S.name, S.Salary);

This the way in which Incremental loading is done using the Merge Command.