July 1, 2011 at 8:09 am
Hello SSIS experts,
In my environment I have 3 servers for MS SQL:
- 1 clustered 2008 R2 server where MS SQL engine and SSMS is installed.
- 1 2008 R2 virtual server where SSRS is installed -- happily running with no problem
- 1 2008 R2 server where SSIS is installed (this will be moved to a virtual server eventually).
I created my first package in SSIS, imported it to SSIS server via SSMS. Run it manually, works fine. The catch is I want to run this on a schedule basis. What is the best way to do it?
1) SQL jobs - nor working because SSIS Agent is not running on SQL server and it cannot run a remote package (I cannot change the environment.)
2) Have a windows scheduler to run a .bat file (the .bat file contain 1 single line of dtexec command)
3) A C# little application which does the same as #2)
4) ???
How would you configure SSIS Server (is it possible?) to offer a scalable solution? Many more packages to come, with different schedules, which needs to be kept track of?
Thank you and have a Happy 4-th of July weekend !
July 5, 2011 at 4:02 pm
Well here is my option 4:
Unless you have a 3rd party job scheduler, I suggest you install the SQL Engine as well on your SSIS server and use the SQL Agent Job Scheduler.
You can setup sql to use very little resources (especially memory) if you are just using it to run jobs set the memory low like 512mb.
Licensing should not be an issue since SSIS alone or with the SQL Engine costs the same (don't quote me on this, MS licensing varies quite a bit). This is what we did.
I like the idea of a custom app but the time and cost involved in that will far exceed installing the sql engine.
July 5, 2011 at 4:16 pm
Tom Van Harpen (7/5/2011)
Well here is my option 4:Unless you have a 3rd party job scheduler, I suggest you install the SQL Engine as well on your SSIS server and use the SQL Agent Job Scheduler.
You can setup sql to use very little resources (especially memory) if you are just using it to run jobs set the memory low like 512mb.
Licensing should not be an issue since SSIS alone or with the SQL Engine costs the same (don't quote me on this, MS licensing varies quite a bit). This is what we did.
I like the idea of a custom app but the time and cost involved in that will far exceed installing the sql engine.
This is correct. Running any of the components on a server requires you to license the server, even if you don't install the full engine.
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[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply