We currently have a single SQL Server Integration Services (SSIS) package that performs an extract-transfer-load (ETL) process to move some data from SQL Server to DB2. On the current production server SSIS is installed on the same server as the database instance and the package execution is managed by the SQL Agent via a Job.
We are upgrading our SQL infrastructure to be clustered and the senior database administrator would like to have SSIS on it's own server, so the ETL server was set up by just installing integration services service onto the machine.
Does the SSIS server also need it's own database instance and/or SQL Agent to manage the execution of the packages?
I'm having some trouble understanding how all the pieces would work in this scenario.
We are using SQL Server 2008 R2 SP1.