Post reply

Executing DTS from VB.net on remote server

  • March 20, 2006 at 7:28 am

    #67399

    Hi,

      I made a VB. NET application who fired an existing DTS on a remote SQL Server 2000. The app just need to change a global variable (which is a filename to process) in the DTS and run the package. That file is physically located on another server, so, I've created a mapdrive between my remote SQL Server and the other server. There is also a few files, physically located on the remote SQL Server, used by the DTS.

    When I execute the package in Enterprise Manager, directly on the remote server, it works fine.

    But when the DTS is fired from my app, it crash with "file not found" error. After severals try-and-error, I founded out that the DTS looks for the files ON MY computer, where the VB app is executed, instead of looking locally on the server.

    I'm trying to solve this problem since 2-3 days. Is there anybody out there who can help me, please?

    thanks for your time,

    Dominic

  • March 21, 2006 at 1:00 am

    #627792

    I think your choices are either

    a) Move the files to a UNC location accessible by both you/your machine and the server e.g. \\myserver\myfolder

    or

    b) Use the VB app to connect to the server, load the DTS package, set the global variable(s) and then schedule the package to run with SQL Agent. This should then run under the context of the SQL Server itself and not your machine.

    there may even be a couple more possibilities that escape me just now.

    Dan
    www.firstcs.co.uk

  • March 21, 2006 at 5:46 am

    #627836

    thanks for your reply and the suggestion.

    I tried the 'B' solution you propose, but it didn't worked either. Maybe I've done something wrong in the process, I don't know.

    Anyway, I've finally solved the problem. I created a StoredProc who call the "xp_cmdshell 'DTSRun ...' method. I don't like it, it's not very clean, but who cares, it works.

    thanks for your help

  • March 21, 2006 at 6:51 am

    #627847

    One of the problems I have found with using xp_cmdshell, is there is no good way to see if the package is currently executing or not.  I have found that by creating a stored proc to create a job and executing it; define the job to delete itself on completion.  You really have a lot more control over when and how your package executes.

    -Mike Gercevich

  • March 21, 2006 at 9:58 am

    #627928

    Yes, you're right. But I managed to get the feedback from my SP who executes the xp_cmdshell method. My software wait 'til the SP is done (with a cute infinite ProgressBar and a timeout) and display the result of the xp_cmdshell.

    You have a lot more control with a SP and a job, but I unfortunatly don't really have the time to change the whole things, my deadline was today.

    thanks all

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply