Is it faster to truncate destination and copy all source data or lookup and keep only non-matching data with SSIS transfer packages?
Is it faster...


It depends on a great many things so there is no single correct straight answer to this:


What proportion of the target rows are you needing to delete, update or insert anew, if you don't use the truncate+replace method
The performance metrics of the source and target systems
The network performance between where your SSIS packages are running and the source and target resources


If you are replacing or updating most of the rows then truncate and recreate will probably be faster, if you only need to affect relatively few then partial updates are likely to be faster but this is not always the case.

If truncate+replace does turn out to be faster (or only slightly slower) you may find dropping all indexes (apart from the clustered index) and recreating them after the copy will improve matters further.


  assuming the majority of rows in each table are not new this week/month


In this instance if you know exactly what timestamp you need data from (this can usually be derived from the target's current data, or you could store the relevant value for future reference after each process) and you can efficiently ask the sources for only the updated data, then this will definitely be faster. Even if for some reason you only know roughly and have to request data a bit further back to make sure you get everything, you will not only be writing less at the destination but will be transferring less from each target (just the recent rows) and from the destination if needing to merge (again, just the recent rows) than you would pulling all the data from the sources.


  I can't currently test which is faster as the initial transfer is still going


You can do some useful analysis at this point though: what seems to be the current bottleneck? It could be:


waiting for data to be read from the source(s) - i.e. their IO subsystem or DB design is limiting the processes speed, in thsi case you'll see high IO at that end from your process and less activity elsewhere
waiting for data to be transferred from the source(s) - i.e. bandwidth is a limiting factor, is that link saturated while everything else is more idle?
processing by SSIS if your ETL is more complicated than "read from here, write to there" - i.e. are you seeing a lot of CPU and/or IO activity from SSIS?
waiting for data to be transferred to the target - as above but in reverse
waiting for data to be written at the target - as above but in reverse
... or of course some combination of the above


Keep watching as the tasks proceed: does it look like the same key bottleneck(s) most of the way through the process?