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.
