SSIS Upsert With Lookup Transform

This is a very common question everywhere;

how can I check if data is exists in destination table then update it, otherwise insert new records.

This Scenario named Upsert in common ( Update / Insert ),

there are lots of ways to do it, but in this post I’ll describe how to do it with Lookup Transform.

First Of All, create sample source file, this is our sample source flat file:

then create a table with this structure in destination database:

now go to SSIS package, add a data flow task, and add a flat file source, point it to the source file, and set Column names in the first data row, also go to advanced tab, and change the data type of column id to DT_I4.

then add a lookup transformation,

and in the lookup transformation editor,in general tab set the specify how to handle rows with no matching entries as "Redirect Rows to no match output" ,

then go to connection tab, and create a connection to destination database, and set UpsertDestinationTable as lookup table.

then go to columns tab, drag and drop the ID column from available inputs to available lookup columns,

Now you can implement INSERT part of operation, as below:

add an oledb destination, and connect No Match Output from lookup transform to this destination,

set the UpsertDestinationTable there, and map columns.

For implementing the Update Part;

first create an update stored procedure in destination database, as below:

create PROCEDURE dbo.UpdateDestinationTable
    @ID int,
    @Firstname nvarchar(50),
    @Lastname nvarchar(50)

    update UpsertDestinationTable
    set firstname=@Firstname,
    where ID=@ID

then add an OLEDB Command transform to the data flow task, and connect the Match Output from lookup transform to the OLEDB Command,

go to OLEDB Command Editor, in connection managers tab, set the connection manager to destination database,

in component properties tab, go to sql statement property, and write this update statement there:

exec dbo.UpdateDestinationTable ?,?,?

Note that question marks are parameter markers, and each of them represent a parameter,

then you can map input columns to these parameters in the column mappings tab,

That’s all.

with first run of package, the source rows will transfer to the destination, all 3 rows will go to no match output ;

result in destination table is :

now change a record in source, and also another record like this:

and with second run of package, old row will go for update, and new row will go to insert

and this is the result in destination db

Reza Rad on FacebookReza Rad on LinkedinReza Rad on TwitterReza Rad on Youtube
Reza Rad
Trainer, Consultant, Mentor
Reza Rad is a Microsoft Regional Director, an Author, Trainer, Speaker and Consultant. He has a BSc in Computer engineering; he has more than 20 years’ experience in data analysis, BI, databases, programming, and development mostly on Microsoft technologies. He is a Microsoft Data Platform MVP for nine continuous years (from 2011 till now) for his dedication in Microsoft BI. Reza is an active blogger and co-founder of RADACAD. Reza is also co-founder and co-organizer of Difinity conference in New Zealand.
His articles on different aspects of technologies, especially on MS BI, can be found on his blog:
He wrote some books on MS SQL BI and also is writing some others, He was also an active member on online technical forums such as MSDN and Experts-Exchange, and was a moderator of MSDN SQL Server forums, and is an MCP, MCSE, and MCITP of BI. He is the leader of the New Zealand Business Intelligence users group. He is also the author of very popular book Power BI from Rookie to Rock Star, which is free with more than 1700 pages of content and the Power BI Pro Architecture published by Apress.
He is an International Speaker in Microsoft Ignite, Microsoft Business Applications Summit, Data Insight Summit, PASS Summit, SQL Saturday and SQL user groups. And He is a Microsoft Certified Trainer.
Reza’s passion is to help you find the best data solution, he is Data enthusiast.

Leave a Reply