You also need to give him read permission (db_datareader) on the source and write permission on the destination (dt_datawriter).
If the user needs to use the TRUNCATE TABLE statement, he also needs db_ddladmin.
If any other objects are used outside of SQL Server (flat files, .exe files, network shares, ...), he also needs read/write/execute permission on those.
If the user is going to create SQL Server Agent jobs, he needs at least SQLAgentUserRole.
SQL Server Agent Fixed Database Roles