By 'where package is running' I mean what CPU and memory resources are used, and where the SSIS needs to be installed. This simple question surprisingly often confuses users, as there are many options to store packages and many options to run it.
The answer is very short - inside the application that started it. SSIS 2005 runtime is in-process object model; to use it you need some process that loads and executes it, the most common hosts are DTEXEC (also used when you create SSIS step in Agent) and DTEXECUI tools. Unless you explicitly call some remote agent that runs package somewhere else - the package runs locally.
- If you run package using DTEXEC or DTEXECUI (Execute Package Utility) - it runs on your workstation, under your account.
- If you run package using BI Dev Studio (BIDS) - also runs on your workstartion, under your account.
- If you run package using SQL Management Studio (SSMS) - again SSMS directly runs it on your workstartion.
- If you execute package programmatically using SSIS API, it runs inside your application.
- If you schedule it using SQL Agent - the Agent runs DTEXEC, so it runs on Agent server, under Agent service account or Proxy Account if you configured one.
BTW, this simple answer implies the location where the package is stored does not matter at all - you can store the package on remote machine, but when you run it using DTEXEC or start package from SQL Server Management Studio, the package runs on your machine where DTEXEC or SSMS runs.
Implications: Besides consuming CPU and memory resources, you also need to install SSIS on the machine where the package runs.
Note: of course, besides the CPU and memory used by the package process itself, it may consume other resources: e.g. Execute Process task may start new process(es); Execute SQL task can send a query to remote SQL server, thus consuming resources of this server while it executes this query; FTP task consumes some resources of remote FTP server; etc.
This leads us to the second question: if I want to run my package on a remote machine, without installing SSIS locally - how can I do it? There are many options, the most common are (1) use SQL Agent, (2) create a custom Web service application. To use Agent you need to create a job on the machine where you want to run the package, configure the job to use the appropriate proxy account, create a step that executes the package. Now you can either schedule the job, or start it manually from SQL Server Management Studio, or start it programmatically by executing sp_start_job stored procedure.
Hint: if you have problems with the package executing under Agent, consult this KB article: