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 http://msdn.microsoft.com/en-us/library/bb510625.aspx , or an UPDATE ... FROM batch command.
You can also check out the http://www.codeplex.com/SQLSrvIntegrationSrv/Release/ProjectReleases.aspx?ReleaseId=19048 or http://www.codeplex.com/ssisctc/Wiki/View.aspx?title=Batch%20Destination&referringTitle=Home available on Codeplex. John Welch (author of the Batch Destination) has a http://agilebi.com/cs/blogs/jwelch/archive/2008/11/07/batch-destination-and-the-merge-destination.aspx which compares the two.
