October 25, 2002 at 3:12 pm
Hello:
When using client side ado code to call a dts routine on an SQL server, is some portion of the processing performed on the client?... Why must the client have a DSN to run DTS code?
Thanks,
Greg
October 25, 2002 at 4:10 pm
All processing that moves data from one object to the next in the DTS package will be done at the client. For example if you have a data pump between two objects, the data will come across the network to the client, and then back up to the server. Of course, calls to stored procedures would be performed on the server. All references to DSNs will be local to the client.
If you want your package to run locally on the server, schedule the package to run under SQL Server agent. When the job executes it will be executed in the context of the account that the SQL Server Agent runs under. If you want to do this as a one time run, you can right-click on the job and click "Execute Job". I would recommend setting a completion notification on the job so that you know when the package completes. This functionality should be exposed through DMO, but I haven't tried so I am not sure.
Hope this helps.
Sincerely,
Mark Cudmore, MCP
Sincerely,
Mark Cudmore, MCDBA
October 25, 2002 at 4:40 pm
Thanks Mark,
I am writting a simple MS Access application that will allow a clerk to manually call the DTS routine (pull data from legacy system to SQL server). Ideally I would like to show the hour glass on the client while the server does the work. I have considered wrapping the dts routine with a job, and calling the job from a stored procedure. The resulting problem is that sp_start_job ends when the job is successfully started, so I would need another sp to continually call sp_help_job until the job/dts routine has finished or failed.
Am I missing something?
--Greg
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply