SSIS - Incremental Data Load

Refer:

Using Temp Tables Across SQL Tasks - SSIS

Refer Source:Incremental Data Load - The SSIS Approach

Destination Connection Manager:

RetainSameConnection  :True

Approach1:

1.Insert,Update

Sequence Container:Data Flow Task

Ole DB Source:          Ole DB Target :

Sort  ( in both)

Merge Join (Left Outer Join)

Conditional Split(Where Clause)

      Insert  Records (  Id  not in Source)

      Update Record( Change record :   where source.name != Target.name)

example:

Select    from source  a left outer join target b on a.id=b.id

update  target b

set  col

from ...

where ...

note: the  update is done 1 rec at a time.delete 1 rec at a time.it is not set based method like

updating all at a time or many records at a time.

2.Delete

TargetDB

LookUP  ( 

OLE DB Command  (  update ... col1=?  )( ?  replaced by parameters set on it).

note:Deleting  the Id  not in staging but in source, in dw its not done,the data is not deleted.

Approach2:SET based approach

Sequence Container

Data Flow Task

Create TEMP Table  (## -  for  Global Temporary Table)

OLEDB Update

---

Data Flow Task:

OLE DB Destination:

    ValidateExternalMetaData:False

note:instead of   "OLE DB Command"  use "OLE DB Destination"

Approach3:Merge &  OLE DB Source & OLE DB Destination.

Sequence Container

Data Flow Task

Create TEMP Table

note:loading the source table  to tempdb of  target server.

MERGE  EMP_TAREGT      AS  T

USING    ##EMP_SOURCE  AS   S

ON (T.ID=S.ID)

WHEN NOT MATCHED BY TARGET

THEN INSERT (ID,NAME,DEPT) VALUES (S.ID,S.NAME,S.DEPT)

WHEN MATCHED AND T.NAME < > S.NAME

OR ISNULL(T.DEPT,'AA') < > ISNULL(S.DEPT,'AA')

THEN UPDATE SET T.NAME=S.NAME

WHEN NOT MATCHED BY SOURCE

THEN DELETE;

note: OLEDB  Source  ,  ValidateExternalMetaData:False ( runtime the table is going to be created.)