Is It Possible To Do Performant Updates With SSIS?

Hello everyone,


I would like to develop an SSIS solution which loads data from large-sized csv files into the database either in REPLACEMENT or UPDATE mode.


In REPLACEMENT mode I delete all the exisitng data (if any) in the corresponding tables and then I do insertions with a few transfomations beforehand. I have accomplished this mission successfully with SSIS as follows:
- Control Flow: Execte SQL Task for performing pre-execution tasks --> Data Flow Task --> Execte SQL Task for performing post-execution tasks
- Data Flow: Flat File Source --> Some transformation components --> OLE DB Destination with FastLoad options.


In UPDATE mode I would like to update existing records in the database. I have found a great deal of articles talking about updating existing records. Based on my readings, here are the statements, I have concluded:
1. If the expected number of updates is high, use a staging table and perform the update as a batch operation from the Control Flow.
2. If the expected number of updates is low, use an OLE DB Command transformation.


Question:
Well, in my particular case all the rows in the source file represent record updates. And I am expecting a large number of update records. Thus, according to the previous conclusions, I should use the staging table alternative.
What I do not like about this alternative is that I will have to insert all the source rows in a staging table first and then I will have to update them all. This seems like too many extra work.

Is there a way to avoid this extra insertion work? I am thinking of something like "FastLoad Update" similar to the "FastLoad Insert", where I directly map input columns to output columns after defining a certain WHERE-CLAUSE?


Thank you in advance.

Samar