I am interested in seeing some DDL and sample data. Does this 'dumping' table also contain all historical records? Are you loading a dimension table ultimately ? One idea refers back to what Phil and Koen mention when they state "set based" operations. Here I illustrate that in a simple image. This is what the SCD wizard generates. According to what I've read most of your records will be new. The image shows that those that are not can be loaded into separate staging tables. From there you perform execute sql tasks to take care of each of these tables. For example, the Historical output will mean that you , I assume . have something like a validFrom , ValidTo date fields in the destination dimension? If an attribute columns value has changed then change the validTo of the destination table record to the current datetime and insert the new record. All this is done in t-SQL. THis will be a lot faster because the whole tables can be loaded into the memory buffer, rather than only one row at a time with the SCD transform. With sample data and DDLs I can illustrate better.

If this is still a perfromance drag then you will have to bypass the whole SCD entirely. But for now these are not to bad to try.