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?
Best Answer
The machine, or in this case SSIS server, that calls the DTSX package will be where it executes, using its local resources (i.e. CPU and RAM) to channel data through the SSIS server. The ETL transformations will be done on the SSIS server, again using its resources. The remote machines with data sources and destinations will still see disk IO or ram increase, but not much else.
Executing ETL jobs on database servers might not be the best idea depending on what you're doing, as this could cause the database server to be slow. Just look at what you're trying to accomplish and go from there.
I've read and used as a reference WROX's Professional Microsoft SQL Server 2012 Integration Services. Great resource if you're going to be using SSIS a lot.
Understanding the Differences between Local and Remote Execution