Lookup Pattern: Upsert

This is a pretty basic pattern where we use a lookup to determine whether we need to update and existing row, or insert a new one. The lookup checks if a key or set of values exists. If the key isn’t found, the row is sent to an OLEDB Destination for the insert. If it is found, it is sent to an OLEDB Command to do the update.

Note, the OLEDB Command transform operates on a row by row basis – so a separate SQL statement will be executed for every row going in. As such, the OLEDB Command can be very slow if you’re processing a large number of rows. An alternate approach is to stage the data, and either update your target table using the MERGE statement, or an UPDATE … FROM batch command.





You can also check out the MERGE Destination or Batch Destination available on Codeplex. John Welch (author of the Batch Destination) has a blog post which compares the two.

Comments (4)

  1. From the Lookup presentation I put together for the MS BI conference in October, here is a series of

  2. The SQL Server Integration Services team added valuable new caching options (and scalability) to the

  3. GD says:

    Can you post your 'Update existing row' function. I'm confused as to the variables used for references to the input table and output table.

  4. angur lu says:

    how do we formulate the update as in regular update, there's a from clause and our update source is actually coming from SSIS task not a table, right?

Skip to main content