August 6, 2002 at 11:46 am
I have long suspected that executing a DTS package from a workstation requires the data to make a round trip from the server, throught the workstation for processing by the package, to the destination. I finally found this documented by M$. Here's an excerpt from:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql2k/html/sql_analservbp.asp
Executing DTS Packages: Which Machine?
Understanding where DTS packages execute can be a problem. For example, if you're moving data from serverA to serverB, using a DTS package stored on serverB, you might assume that the package would execute on serverB. This is not necessarily the case.
DTS packages execute on the computer that launched the execution – regardless of where the package is stored and where the data resides. Data makes a round trip to the package machine, and any package steps such as ActiveX scripts also execute on that machine.
In many cases, particularly early in the development cycle, the machine that launches the DTS package is a desktop machine. This can lead to suboptimal performance for the DTS package.
One way to avoid this is to use Terminal Server during the development cycle, to run the package from an appropriate machine. During production, schedule jobs using SQLAgent.[/quote
August 6, 2002 at 12:31 pm
Yepper, this is the case. Can cause frame of reference issues (like local paths).
It's a design decision. I run many from the server, but it can be like a program. I might want to run it from some dedicated workstation to avoid the CPU load on the server.
Steve Jones
http://qa.sqlservercentral.com/columnists/sjones
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
August 6, 2002 at 1:21 pm
Its a tremendous headache. I guess I understand why its that way, but it causes no end of issues. Package gets designed against shares that dont exist on the server (or vice versa), different permissions, etc. Looking at it as a program is a much better metaphor for understanding where it will execute.
All in favor of giving all developers TS access to the server for building packages?
Andy
August 6, 2002 at 2:33 pm
Andy,
TS access to the server? HA! Like THAT will ever happen!
The paths issue is why, as a developer, I NEVER use drive letters. If you use a fully qualified UNC path, e.g. \\mysever\myshare you avoid a lot of headaches. To get around the permissions issue, scheduled jobs here run as a domain admin.
Steve,
Very true as long as you weigh out server CPU drian vs network strain. These days, I'd guess the pipe is going to be the performance bottleneck.
August 6, 2002 at 2:38 pm
We actually do a combination of both Steve's and Andy's idea in that we have a dedicated server which EM is installed on and we give our developers TS connection to that server. That way they can establish their own registered servers based on their permissions and develop and run their DTS packages there. Works well and lasts a long time.
See, dreams really do come true Toto!
David
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
August 6, 2002 at 7:41 pm
We have a workstation set up with personal edition that we use mostly for the scheduler, but TS runs on servers (we're not using XP yet). Our admin has been looking at Sunbelt Remote Administrator for situations like this, anyone used it?
Andy
September 9, 2002 at 11:49 am
Maybe you've figured it out by now but in case you haven't, what you guys need to do is have your page call a stored procedure which in turn calls the DTS pkg, it doesn't go thru the client. At least thats my experience.
A.S.
MCSE, ASE, DBA
Webmaster
Applications Developer
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply