I actually saw a very similar result when updating a large number of rows in a table. I had an SSIS package which updated one or two columns of many rows in a table. It would take hours to use the SQL Command component (and run a stored procedure for each row). So instead I inserted the PK and values of the rows that needed to be change into a temporary table with the same columns and data types as the table that needed updating. After that I ran a stored procedure that was something like this

UPDATE MyTable

SET Col1 = ISNULL(MyTempTable.Col1, MyTable.Col2)

FROM

MyTable INNER JOIN MyTempTable

ON MyTable.PK = MyTempTable.PK

(I know my code isn't right, but I think the principle is clear.)

Doing this takes as little as 5 minutes, when previously I would have waited hours to do the updates one at a time.

I appreciate that the overhead of SSIS's connections almost cetainly exaggreates the benefit, but the basic idea is the same. Using a join instead of looking up the individual rows.