I recently started using SSIS in my internship and I am still learning. We have a dedicated SSIS server that executes all of the ETL jobs and does not hold any other databases. We have many SQL servers as source and destination.
How does SSIS manage data transfer and transformations from source to destination when it is just executing the job and package? Does it establish a direct connection between the two or is the data channeled through the SSIS server?
Do you recommend adding SSIS to sources and/or destination servers and running jobs locally?