July 20, 2006 at 10:14 am
I just started a new job and inherited a SQL Server 2000 database server with several DTS packages already created. I'm running a very basic package - it imports several hundred thousand records from a delimited text file and appends them to an existing table. There is no data transformation - it's a direct field-to-field import. I'll probably change to a bulk insert task later on, but right now the package is using a data transformation task for the transfer. The text file that's being imported resides on the local drive of the SQL Server. When I run the package from Enterprise Manager on my workstation, it takes approximately 30 minutes to run and frequently errors out. When I run it from Enterprise Manager on the server, it runs in less than 5 minutes and never errors out. Does anyone have any thoughts on why there's a difference? Since the DTS package and the text file both reside on the server and there's no data transformation involved, I would have thought it would run at the same speed regardless of where it was kicked off.
Thanks,
Sheila Nelson
July 20, 2006 at 10:24 am
Hi Sheila,
A package executed on your workstation actually runs there. That means the data will be pulled from the text file on the server, throught your workstation, and back to the database on the server. Performance is almost always better if you can connect directly to the server via Terminal Services or the server console.
Greg
Greg
July 20, 2006 at 12:55 pm
Thanks! I really appreciate the clarification on that.
Sheila
July 24, 2006 at 3:49 pm
When you run the package from your workstation it is using your workstation as it's context rather than the server's. Any file paths that are relative to the server will not exist on your workstation and unless they use the FQDN you will get errors. Another reason to run dts packages from the server. Hope this helps.
July 25, 2006 at 5:51 am
Thanks. Unfortunately, the server is not in a convenient location, so I'm still trying to figure out a way that I can do this from my workstation. What about terminal services? Is there any reason not to use terminal services to access the server and execute DTS packages?
Sheila
July 25, 2006 at 9:47 am
I use Terminal Services all the time to execute DTS packages, particularly those with connections that use ODBC DSNs because the DSN may be different on the server than on my workstation.
Greg
Greg
July 25, 2006 at 11:25 am
Great. I'll give that a try then, once I figure out how to turn it on on the server. Thanks.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply