Hi all. Hope I'm posting in the right forum.
We currently schedule SSIS packages using the Windows task scheduler, but we want to use the SQL agent for this. The most important reasons are.
- Easier maintenance of jobs by the DBA instead of the system administrators who administer scheduled tasks
- Easier monitoring of jobs via job history
- Easier to define flows in SQL agent job steps for dependent packages. First execute package X and then Y etc.
In our configuration we have 2 separate servers.
- DB server. Purpose: only for serving data (not in DMZ for security reasons. This is a strict security policy which we can not change)
- JOB server. Purpose: application server. Contains scheduled tasks and serves SSIS packages (in DMZ)Problem
SSIS Service is installed on the job server but not on the database node.
First I tested scheduling an agent job on the DB server which calls a package hosted by the JOB server. This works, but the SSIS package is acutally run on the DB server instead of remotely on the JOB server. This is a problem because the DB server is not in the DMZ and can not access external sites. So our packages can not be run correctly.
Workarounds for remote scheduling via sp_startjob are complex and have several additional problems.Solution?
A solution is just to stick with old scheduling and run the packages directly via dtexec. However we then loose al of the SQL agent job advantages.
Another solution is to install another SQL instance on the JOB server itself just for scheduling the SSIS packages. This is in my opinion the best solution, but it will costs us an extra SQL license!
It also violates the SQL instances not in DMZ policy. However because we don't store any data (besides master databases) here the risk is decreased.Advice?
I really need some good advice from the experts on this
MS-SQL / SSIS / SSRS junkie
Visit my blog at dba60k.net