dts on different machines

dts on different machines

Post by sham » Thu, 23 Nov 2006 02:52:02


Hi to all,

I am new to dts and sql server and wish to know if this is possible.

We want to know if sql server allows you to run dts's on different machines?
What I mean is some sort of architecture that allocates a dts to a client
machine that does the work and another dts is assigned to another client
machine. Each machine does their own work. I do not want to run the dts's on
the server (i.e. some sort of farming solution).

Is this possible?

Sham.
 
 
 

dts on different machines

Post by Allan Mitc » Thu, 23 Nov 2006 06:22:24

Hello sham,


This is often accomplished by starting a SQL Server Agent job on the other
box that fires the package.


Regards

Allan Mitchell
Konesans Ltd
T +44 7966 476 572
F +44 2071 008 479
http://www.yqcomputer.com/

 
 
 

dts on different machines

Post by sham » Sat, 25 Nov 2006 20:29:49

Hi Allan,

Thanks for the reply.

Can you give me more of an insight in how this works?

For example, I have a main server which has all the packages. Then we have a
number of boxes which have one SQL server agent on which we can process each
of the packages that are allocated to them. Am I correct so far?

Who allocates the packages to each agent? In other words, if agent 1 is
processing package A, then agent 2 cannot be assigned this package and must
be given another one. Does this type of processing exist with dts? There
must be some sort of server processing that does the scheduling of the
packages to the agents?

Also, if an agent dies (i.e. machine unplugged or n/w problem), doe the rest
of the agents have work balanced among them?

We need to decide on an architecture that will allows us to import a large
number of logs from a number of machines with the view that later we may
need different machines with an agent to do the processing.

Any help will be greatly appreciated.

Sham.
 
 
 

dts on different machines

Post by Allan Mitc » Sun, 26 Nov 2006 15:47:22

Hello sham,

You will use Agent on/Windows scheduler on many machines to call into DTSRun.
The DTSRun command line will be pointing to your packages wherever they
may be. Remember the storage location of the packages is immaterial, it
is where you ask to run from.

To be able to fire the packages using DTSRun you can simply register the
DTS dlls

http://www.yqcomputer.com/

The allocation will be done through you or whatever builds your cmdline string
To be able to know if one scheduler had picked up a package and was running
with it you would need to build that functionality yourself which could be
as simple as a log table.





Regards

Allan Mitchell
Konesans Ltd
T +44 7966 476 572
F +44 2071 008 479
http://www.yqcomputer.com/