using file on server | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

using file on server

hey guys, I’ve noticed this a long time ago. If we execute a DTS on our machine via Enterprise Manager, DTS uses our own filesystem to find the file we specified and not the on the server’s filesystem. However, if we execute queries like BULK INSERT via Query Analyzer running on our machine, it uses the server’s filesystem which is nice. My issue now is that, I have to connect to our db server via terminal services and run EM from there. It’s ok with me, however, there are people in other team that also needs to execute the package but is not allowed to terminal services to the server. What is your solution to this? Any help would be greatly appreciated. Thanks!

You could create a stored procedure to run the DTS package. Then the user could execute the proc remotely and it would run on the server side. Or you could create a job (without a schedule) and users could run the job using sp_start_job – again you could put this inside a proc to make it easier. Or in the DTS package you could create all the paths to files as UNC paths. Then it doesn’t matter where it is executed from – through if you are on different domains this may not work. Simon