running ssis package with a remote sql agent?

hi all,

thanks for the valuable information all the time!!! saved me a lot of time...

our team developes a system for text data improvement using ssis .

we have a few heavy packages that we want to execute on two separate "SSIS servers" that will be dedicated to runnung these packages only, and repeatedly. the main sql server will be placed on a different server machine .

my question is:

what is the best way to do this?

if we schedule these packages as a job of the Sql Ajent- does that mean that the packages will be executed on the sql server machine (which is not what we want) ? or could we define a remote machine to run the package on, and specify our "SSIS servers"?

or- should we use a simple scedualer on the "SSIS servers" using a dtexec command? but then i loose the benefits in using the sql agent- such as logging, notifications, etc.

do we need to install sqlserver on these "ssis servers"?

thanks for your replies!

To use SQL Server Agent in this scenario, all you need to do is ensure that SQL Server Agent is installed and accessible somewhere on the network. It need not be installed on your main SQL Server or the SSIS servers.|||

thanks for your reply! could you be more specific?

how do i use sql agent if it is installed on a different machine, to run the package on my computer?

i just found in kirk haselden's book a "caution" on this subject, it says:

"SQL Server Ajent service relies on the sql server service. if you intend to use sql ajent to schedule package execution, you need to have SqlServer installed on the machine as well, if you have some policies that don't allow SqlServer on the same box with integration services or otherwise govern the installation of SqlServer that prohibits you from running both servers on the same machine, you need to use the windows task scheduler or some other tool..."

can you explain this to me?

|||

That means that both services: SQL Server agent and SSIS needs to be running in the same machine where you are trying to execute he package via SS Agent. So you have 2 options, install the SS agent & SSIS in your computer or install the SSIS only and use other job scheduler tool.

May I ask why you want to schedule a job from your PC instead from a server?

|||

hi rafael, thanks, that as what i thought.

the reason to run the packages on a different machine is the load balancing- we are talking about a very heavy process, that is supposed to be working repeatedly, all the time. we don't want the SQL server performence to slow down.

what do i loose if i don't use the Agent to run these proccesses?

|||

rebecca M wrote:

what do i loose if i don't use the Agent to run these proccesses?

I would say the ability to put it under a schedule.

But, I have you considered to install SQL Agent job in the same machine where you are intending to xecute the package from?

|||

Rafael Salas wrote:

I would say the ability to put it under a schedule.

and what about monitoring, logging, and such?

Rafael Salas wrote:

But, I have you considered to install SQL Agent job in the same machine where you are intending to xecute the package from?

that is exactly my question: could i install SQL Agent on that machine without installing SQL Server? any licence issues?

|||

rebecca M wrote:

and what about monitoring, logging, and such?

The looging information provided by SQL server agent would be about if the job was executed succesfully or not. Do not confuse with the SSIS logging that is define inside of the packages.

rebecca M wrote:

that is exactly my question: could i install SQL Agent on that machine without installing SQL Server? any licence issues?

I *think* SQL Agent requires DB componnets and I think you would require a license on that box as well.

|||SQL Server Agent relies on the SQL Server Service. The SQL Server Service is part of the SQL Server Database Engine. Therefore, SQL Server Agent cannot operate without SQL Server Database Engine.sql