Running SSIS Packages "outside" of SQL Server?
Posted on 2011-10-05
This might seem like a weird question, but do SSIS packages necessarily have to run within SQL Server (ie: under SQL Server service)? I know you can choose to *store* the package inside SQL (msdb) or on the file system, but I'm talking purely in terms of execution.
Reason I ask is I have a client who uses SQL Server (2005) Agent to run an SSIS package every minute, every day. After at least 6 days, the SQL Agent job will "break"... no errors are logged, and when they try to restart SQL Agent, it doesn't do so cleanly. They can try to kick off the job manually, but it won't run. The only solution is to reboot the server, then everything works fine again for another 6-7 days.
They are asking about "externalizing" the SSIS job - use Windows Scheduled Tasks for scheduling, and whatever other method (eg: OSQL?) to execute the package. Up front the 6-7 day lag suggests maybe a memory leak is to blame. But my question is, even if I externalize the triggering and use another method (eg: dtexec or dtexecui), do those methods still cause execution to be run under SQL Server service?