Results 1 to 10 of 10
  1. #1
    Join Date
    Feb 2009
    Posts
    4

    Unanswered: Execute EXE from DTS package on Remote Server

    Hi,

    I have the following problem/scenario!

    SQL 2000 DTS package needs to call an EXE on a remote server (UNC/IP access OK). The EXE MUST run on the remote server to do it's stuff (it has reference dependancies on the remote server to be able to run) and the DTS MUST wait for the EXE to finish before continuing.

    Using an "Execute Process Task" runs the exe from the SQL server (not from the remote server) and fails - due to the dependancies etc. Trying to run from WSH in an Active X also seems to have this affect.

    I could use a script on the remote server to run the EXE's and then call the rest of DTS - but I am not happy on that solution due to permissions etc.

    Is there a way to run the exe's on the remote server know when they have completed?

    Many thanks!

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Only way I can think of to do this is to set up the remote EXE as a scheduled task with no schedule. The DTS package will need to have an ActiveX script that invokes a bunch of WMI commands to trigger the scheduled task on the remote machine. Not even sure if that is possible, but it is what I can think of.

  3. #3
    Join Date
    Feb 2009
    Posts
    4
    Thanks - that, alas, is my fall back position at the moment! The problem here I think is that I cannot then tell when (or if) the scheduled task has completed - and I cannot proceed with the rest of the tasks until the exe(s) have completed.

    I could conceivably wait xx seconds and assume success - but that is a little frought with danger!

  4. #4
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    If you have control over the EXE, you can have it drop a "marker file" to indicate success or failure. Or even make that a second part of the scheduled task.

    If not, you will likely have to make a .BAT wrapper for the EXE, which will accomplish the same thing based on the value of %errorlevel%.

  5. #5
    Join Date
    Feb 2009
    Posts
    4
    Alas I do not have any control over the exe - but yes I could drop a marker file I guess.... seems darn convoluted, but i guess that is often the way!

    Thanks again.

  6. #6
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    A batch file was going to be my suggestion as that's exactly what I've implemented on our of our scheduled SSIS packages!
    George
    Home | Blog

  7. #7
    Join Date
    Feb 2009
    Posts
    4
    Not sure if I am pushing this post too far now... but Plan B in development!

    I am using a .vbs (WSH) to run the exe's, wait and then run the DTS. Locally (on the SQL) server DTS runs with no problems.

    However running the .vbs on the remote server works only if the DTS itself doesn't do anything to the database (i.e. it just returns a "hello world" from an ActiveX script inside the DTS), but as soon as a "select" is used in the DTS then it doesn't run (debugging tools seem sparse!).

    This sounds like permissions (and almost certainly is) but as the DTS actually runs remotely I am not sure what permissions are causing the problem.

    It runs this in the vbs script:

    Set oPkg = CreateObject("DTS.Package")
    oPkg.LoadFromSQLServer sSQLServer,sSQLUser,sSQLPassword,0,"","","",sDTSPk g
    oPkg.Execute()

    Where all vars (sSQLServer,sSQLUser,sSQLPassword, & sDTSPkg) are correctly defined (otherwise it wouldn't run locally).

    I assume using a batch file would probably run into the same problems - though I haven't tried this yet.

    Feel free to tell me I am a fool for trying this

  8. #8
    Join Date
    Jun 2004
    Location
    Long Island
    Posts
    696
    I ended up getting a product called "ActiveBatch" to handle cross server jobs and execution of jobs (whether .exe, .bat ,etc. on local servers) as part of a plan in which jobs are dependent on each other.

  9. #9
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Scratch my reply... I've just gone digging back through to find out exactly what I'd done before and I had used a "master" batch file to call out to other batch files that executed CSVDE commands... So not quite what you're trying to achieve.

    Sorry if my first post was misleading
    George
    Home | Blog

  10. #10
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    I blame the architecture, myself. There is a master scheduling system from Tivoli called TWS Maestro. This can run command line scripts, executables, and whatnot on multiple machines. You can even create schedules to do exactly what you are trying to do here. I am not sure if you can accomplish the same thing with SQL Server Agent's master schedule capabilities, though.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •