Perform Upsert ( Update/Insert SCD1 ) by using Lookup Transformation - SQL Server Integration Services(SSIS) Tutorial

Scenario:

Let's say we have to load a dimension table from text file. Our business Key is SSN. We need to insert new records depending upon values of SSN column, If any new then we need to insert this records. If SSN already existing in Table then we need to find out if any other column is changed from Source columns values. If that is true then we have to update those values.

What we will learn in this video

1. How to Read the data from Flat file in SSIS Package
2. How to perform Lookup to Find out Existing or Non Existing Records in Destination Table From Source
3. How to Insert new Records by using OLE DB Destination
4. How to update existing Records by using OLE DB Command Transformation in SSIS Package

Solution:

Le't create a table that need Update/Insert operation

CREATE TABLE dbo.DimCustomer
  (
     CustomerID INT IDENTITY(1, 1),
     SSN        VARCHAR(11),
     FirstName  VARCHAR(50),
     LastName   VARCHAR(50),
     Address    VARCHAR(100),
     LoadDate   DATETIME,
     UpdateDate DATETIME,
     LoadedBy   VARCHAR(50),
     ModifiedBy VARCHAR(50)
  )

Sample Data we will use in Source File
SSN,FirstName,LastName,Address
000-000-001,Aamir,Shahzad,NJ USA
000-000-002,John,River,NC USA


Query used in the OLE DB Command Transformation

Update dbo.DimCustomer
SET FirstName=?,
LastName=?,
Address=?,
UpdateDate=?,
ModifiedBy=?
WHERE SSN=?

How to load Slowly Changing Dimension Type 1 by using Lookup Transformation in SSIS Package - SSIS Tutorial

  Related Posts / Videos on Lookup Transformation 

• How To Use Dynamic Query in Lookup Transformation in SSIS Package
• Use Stored Procedure with Parameters in Lookup Transformation ( Full Cache Mode) in SSIS Package 
• How to Force Lookup Transformation to Work as Case In-Sensitive in SSIS Package
• Does Lookup Transformation perform Left Outer Join or Not in SSIS Package
• How Null values will be matched in Lookup Transformation in Diff Modes(Full Cache,Partial or No Cache)
• How to Handle "Row yielded no match during lookup". Error in SSIS Package
• How Lookup Transformation works for Duplicate Records in Reference Data Set
• Detailed Demo on Lookup Transformation Modes ( Full Cache, Partial, No Cache) by using SQL Server Profiler
• Diff between Inner join ( Merge Join Transformation) and Lookup Transformation ( Matched Output)