Orlando Colamatteo (1/23/2016)
Would it benefit you to improve in the 43 minutes? 5 million rows is more than your average data load but it is possible to process that many rows a lot faster than 43 minutes, depending what is being done. Sometimes a design decision can shave hours off like what you just did moving from row by row processing from SSIS to the database to a set based process rooted in T-SQL. Other times redesigning or tuning T-SQL can shave hours off. Have you profiled the process end to end to find out which step is taking the longest? Can you lay out what the process is doing end to end for us?
Very true. 5M rows in 43 minutes leaves the impression there is room for improvement. In your merge, are the join columns indexed? It might help to build/rebuild an index on that stage table after a load.
----------------------------------------------------