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
- How to Read the data from Flat file in SSIS Package
- How to perform Lookup to Find out Existing or Non Existing Records in Destination Table From Source
- How to Insert new Records by using OLE DB Destination
- 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
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