If SSIS 2005:
you can use a left outer join in your data source. Join your source (staging) table with the destination (dimension/fact table) with the no lock hint. If your indexes are placed correctly, it should have good performance. Select the surrogate key from the destination table.
After the source, use a conditional split to see if a record is an update or an insert.
Insert --> SK_Destination IS NULL
Update --> SK_Destination IS NOT NULL
Write the inserts immediately to the destination (hence the no lock hint, or you could get locking issues).
Write the updates to a temp table.
After the data flow, perform a set-based update by matching the temp table to the destination table using the surrogate key you found. (this is much more performant than using an OLE DB Command in the data flow!)
If SSIS 2008:
use the MERGE statement