Where is the package running?

When I ran a package directly on the machine with sql 2005 installed it ran at a fine speed. But when I used Remote Desktop to access the same client and run the same package it was much slower.

1. I think that the package is running in the memory\cpu of the local machine. Is this correct

2. Is there any way for the remote client to run the package in the server's memory\cpu

TIA,

barkingdog

Answer this question

Where is the package running?

zaynun hammoud
Yes, if you have your own asp.net web service - it will be fine as well.
wrade
Thank you very much Michael.
musicandstamps
Well, yes. You can write such a web service, or define HTTP endpoint in SQL Server and call Agent using SQL's web service.

For execution location, it does not matter where the package is stored, what is important is where the program that runs it (usually DtExec) is located.
Airan
Well, the package is executed on workstation of course. SSIS does not care whether you're connected to workstation using real terminal or remote desktop - everything runs on the workstation, using its CPU and memory.

The slowdown might be due to delay caused by all the graphics I/O in BIDS which is slower due to network latency caused by remote desktop connection. BIDS does cause some overhead due to debugging output and tracing; when measuring package performance it is better to use DTEXEC.EXE.

myDisplayName

Michael Entin - MSFT wrote:

1. Yes, the package runs on the same machine where you run the wizard.

2. You may save the package to a file or SQL server, then create an Agent Job on either ServerA or ServerB that will run this package and start the job. Then the package will run on machine where you've create the job.

isn't it also true that web services or .NET remoting can be used to remotely run a package stored in the file system on a remote server

sashisme
I would like to get a clarification on your explanation.

Let's say I have ServerA, ServerB and WorkstationC. I'm using the "SQL Server Management Studio - SQL Server Import and Export Wizard" in WorkstationC to copy a few million rows in multiple tables from ServerA to ServerB. I noticed that while the process is running, the network traffic & processor usage in WorkstationC is close to 100%.

1. Does this mean that all data goes from ServerA to WorkstationC to ServerB

2. If the answer to the previous question is yes, is there another way to do this Import/Export without having to use a Remote Connection
SandpointGuy
How do you run the package If you use DtExec, or DtExecUI, the package runs on the same machine when this process runs (actually inside DtExec[UI] process).
robrich
I used Windows Remote Desktop to go to my workstation (with sql 2005 and SSIS installed). Then I created a new package in BIDS (using the remote client), selected the Start (green triangle) option of BIDS, and let er rip.

The remote client does not have sql 2005 or VS.NET installed on it has limited RAM.

Barkingdog

Marek Zgadzaj

Michael Entin - MSFT wrote:
Well, yes. You can write such a web service, or define HTTP endpoint in SQL Server and call Agent using SQL's web service.

For execution location, it does not matter where the package is stored, what is important is where the program that runs it (usually DtExec) is located.

thanks for your quick response. actually, i was considering a situation where a package is stored in the file system on a remote server and have it run on that server. is it necessary to use agent or dtexec for example, can i call an asp.net web service that uses .NET to execute the package and have it run on the remote server (provided that the ssis assemblies are installed on the remote server)

Sun_Shine

Sal Young wrote:

I would like to get a clarification on your explanation.

Let's say I have ServerA, ServerB and WorkstationC. I'm using the "SQL Server Management Studio - SQL Server Import and Export Wizard" in WorkstationC to copy a few million rows in multiple tables from ServerA to ServerB. I noticed that while the process is running, the network traffic & processor usage in WorkstationC is close to 100%.

1. Does this mean that all data goes from ServerA to WorkstationC to ServerB

2. If the answer to the previous question is yes, is there another way to do this Import/Export without having to use a Remote Connection

1. Yes, the package runs on the same machine where you run the wizard.

2. You may save the package to a file or SQL server, then create an Agent Job on either ServerA or ServerB that will run this package and start the job. Then the package will run on machine where you've create the job.

Where is the package running?

Answer this question