On my work i have a situation that we have a three tier setup:
We work with SSIS packages which are stored in Database Server (on a share) but are called from a external scheduler in the application server. When we run the DTEXEC from the Application Server, the consumption of memory and cpu are on the Application Server. Is it possible and how to start the proces(DTEXEC) on the database server, and call it from the Application Server, so the consumption of the CPU and Memmory are on the database server>
Manny thanks Erik
Running an SSIS package is constrained to dtexec.exe, using the ManagedDTS assemblies, running it from the SSISDB, running a SQL Agent job (which is really just using dtexec.exe).
If you've used the project deployment model, running the packages out of the SSISDB is calling a few stored procedures.
Otherwise, package deployment model means you'll want to figure out what makes sense for your environment and skill set.
I find SQL Agent jobs are the easiest but there are limitations (asynchronous nature of starting a job, concurrent execution, etc)
Remoting (PowerShell/PsExec/WMIC/.NET) seems the next logical step to launching a remote process
Earlier answer on the subject https://stackoverflow.com/a/75289585/181965