Unanswered: Execute EXE from DTS package on Remote Server
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?
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.
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!
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
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.
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.
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.
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.