The Design of Software (CLOSED)

A public forum for discussing the design of software, from the user interface to the code architecture. Now closed.

The "Design of Software" discussion group has been merged with the main Joel on Software discussion group.

The archives will remain online indefinitely.

SSIS: Where should I run the package from ?

I want to move SQL Server 2005 data from server1 to server2 using SQL Server Integration Services.

(1) If I run the SSIS package on server3, will that take longer than if I run it from server1 or server2 ?

(2) If I run the SSIS package on server3, will the data have to go via server3 on its way from server1 to server2.

(3) Is there an optimal server to run from package on, or does it not matter ?
Craig HB Send private email
Tuesday, April 24, 2007
Yes... er... no.

Sorry, I can't give specifics because you didn't give specifics. 

If your dealing with a lot of data going into (or out of) the SQL Server, you can benefit from running everything on the same machine.

But you can also benefit from putting different parts on different machines.

I'd suggest reading the "Tuning the Dataflow Task" article found here:

It gives you several good guidelines to follow to get the best performance.

EX (Note, this is specific to me, so your mileage may vary).

I was running a process from my local machine that interfaced with a SQL 2005 box on the network.  It was taking 3 hours to process the files (scrub, then straight import).

When I moved that to the server, it took about 1 hour.  I then changed the connection to use a local only connection (uses local pipes, and not network).  It dropped to about 20 minutes.

So, for me, it was well worth the move.
Eric D. Burdo Send private email
Tuesday, April 24, 2007
1 - Most probably, but if the task simply executes an SP on server 1 which inserts into a linked server2, then the data will never go through server 3

2 - Most probably, which is why 1 will take longer

3 - Depends on the exact operation, but a straight copy of a table will generally perform the same
Cade Roux Send private email
Tuesday, April 24, 2007

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics
Powered by FogBugz