Lookup Transform: performing the Upsert scenario

The Lookup Transform searches for matching records in a reference table. There are lots of uses for this transform; while loading data into a data warehouse, when a fact table gets loaded with data coming from sources with natural key(s) of dimensions, those natural keys should be looked up in the dimension table and their equivalent surrogate key fetched and loaded into the fact table.

There are also many times when an Upsert scenario is needed. Upsert means Update existing records and Insert new records. We will take a look at an Upsert scenario by resorting to a Lookup Transform and an OLE DB Command Transform. So our example begins in this recipe and will end in the next recipe, and these two recipes together demonstrate an Upsert.

Getting ready

Download Person.csv and Person – second data file.csv from the source files of this book and create a table in the PacktPub_SSISbook database with the following script:

CREATE TABLE [dbo].[SalesPerson...