A well designed package will never write the data to disk and the data will remain entirely in memory between the source and destination.
As J.D. indicates, there are three players in this game: The source, the destination and the machine coordinating the data movement. You could have DB2 LUW running on a windows Server and also host SQL Server on that same box, Box1, but if you take an execution server running, Box2, the data will stream off of Box1 to Box2 only to flow back to Box1. The data packets sure enjoyed the journey but processing would be faster if it never left.
Depending on your situation, it might be a good idea to run the SSIS package on the Source or Destination server and eliminate an extra network hop but that may foul up other things.
Not only should total server memory be of concern, but you can also spill to disk. That's the "worst" sin that can happen with SSIS as your performance is going to the toilet. SSIS is an in-memory Extract, Transform, and Load engine and if it cannot keep the data in memory, it pages to disk so now you paid for
- disk access to get the data from the source,
- write and then read it wherever it is being processed (write down the excess but eventually, it's time to use it so we have to read it back in to the pipeline)
- pay for it to be written to disk at the destination.
Ouch town, party of you. Tim's article calls out the need for memory, the patterns you might use in your packages that can impact available memory and if he didn't mention it, the data types (max types, CLOB/BLOB) can also cause those disk spills.