Scott Coleman (8/19/2014)
I just reread your original post. For data transfer between databases on the same server, the Execute SQL task with INSERT/SELECT should be faster than an SSIS data flow because SQL Server controls the entire process. If the SSIS package is running on a different server, INSERT/SELECT will be much faster because the data doesn't have to make a round trip over the network.
But you can still have some advantages by using the batch inserts used by SSIS. It will depend on the size of the information that you're using. Remember that a huge insert will need a bigger log and multiple smaller inserts (in a simple recovery mode) can reuse the log.
I'd say that it depends. 🙂