Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SSIS package execution remote vs / local


SSIS package execution remote vs / local

Author
Message
peterjonk
peterjonk
SSC Rookie
SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)

Group: General Forum Members
Points: 41 Visits: 611
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 Wink

__________________
MS-SQL / SSIS / SSRS junkie
Visit my blog at dba60k.net
Evil Kraig F
Evil Kraig F
SSCertifiable
SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)

Group: General Forum Members
Points: 5715 Visits: 7660
You've hit pretty much all the nails on the head for this. You either need to license a server to your job server, or run it via a foreign call to dtexec, which is only available via windows scheduler if you're not purchasing/installing more software.

There are other scheduling softwares out there that are relatively cheap that you could give the DBA's access to without opening up windows scheduler if you wanted, and they are worth a day or so of investigation just to know what's available.

But, basically, yeah. You're screwed. It's part of the licensing of SQL Server.


- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables

Twitter: @AnyWayDBA
peterjonk
peterjonk
SSC Rookie
SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)

Group: General Forum Members
Points: 41 Visits: 611
Thanks for your feedback. Well it's good to see that my assumptions were right.
I think we have a license for System Center Orchestrator which can handle the scheduling. But I also want to test if we can just install a standard edition for scheduling and still use the enterprise SSIS executables. Otherwise the addional license will be very costly just for scheduling jobs.

__________________
MS-SQL / SSIS / SSRS junkie
Visit my blog at dba60k.net
Lynn Pettis
Lynn Pettis
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24276 Visits: 37987
Actually, it should have cost you additional licensing to install SSIS on a separate server. The licensing for SQL Server does not allow installing different components on different servers using the same license.

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search