UPSERT in SSIS

See SQL Server 2008 - Using Merge From SSIS. I've implemented something like this, and it was very easy. Just using the BOL page Inserting, Updating, and Deleting Data using MERGE was enough to get me going.

Apart from T-SQL based solutions (and this is not even tagged as sql/tsql), you can use an SSIS Data Flow Task with a Merge Join as described here (and elsewhere).

The crucial part is the Full Outer Join in the Merger Join (if you only want to insert/update and not delete a Left Outer Join works as well) of your sorted sources.

followed by a Conditional Split to know what to do next: Insert into the destination (which is also my source here), update it (via SQL Command), or delete from it (again via SQL Command).

1. INSERT: If the gid is found only on the source (left)
2. UPDATE If the gid exists on both the source and destination
3. DELETE: If the gid is not found in the source but exists in the destination (right)

I would suggest you to have a look at Mat Stephen's weblog on SQL Server's upsert.

SQL 2005 - UPSERT: In nature but not by name; but at last!