The MS build-in SCD component is for example not that efficient. It uses the OLE DB Command to perform the updates, which means that if a million rows have to be updated, a million different updates will be issued against the DB, instead of one big update. I'm not a DBA, but I think that is not so good for performance, locking and transactions.
This is true, but the SCD component should only be used for dimensions, and while dimensions can have a million rows, most don't, and even those that do won't need to updated to that extent on a given download. I would say use it except for the unlikely circumstance you describe above.