DTSRun remote execution

Hi,

I am trying to figure out a way for a machine other than the SQL Server 2000 machine hosting my DTS jobs to fire off a DTS job on request. The jobs are scheduled, but I want to be able to fire them off remotely if the schedule fails. I don't have access Enterprise Manager on the second machine I want to start the jobs from.

An added restriction I have is that I can only execute command line shell scripts on the second machine. This could be a batch file, an executable or any other function available from the command line.

In an initial attempt I made the SQL Server Binn directory available via a restricted network share so the second machine could access the DTSRun.exe file. I then created the DTSRun commands and set them up to run. This worked fine, except I ran into another complication. The DTS Jobs I am running make use of an ActiveX DLL and an ActiveX Control (OCX). These files are registered on the server, but since using DTSRun forces execution to take place on the machine issuing the DTSRun command, the jobs fail because the components are not installed on the second machine.

I would like to try and aviod installing these components on the second machine and was hoping someone could suggest an alternative method for remotely fireing off these DTSJobs.

Any help would be greatly appreciated!

Thanks!
- BradMy first suggestion would be to use a VBScript that uses ADO to manually force execution of the remote job that runs the DTS package.

Just about anything else (SP, CmdExec, etc) is going to run the DTS in the context of your client which may not be desirable for reasons other than the registered ActiveX controls you mention.

Another option might be to create a job or DTS package that checks the original and makes sure that it has run (and executes it if it has not). Or you could simply add this as an additional error check into the original DTS package (set a retry counter and a Global Variable with the maximum number of retries). You would need to examine the business requirements and design a strategy that suits your environment.

Regards,

Hugh Scott

Originally posted by BradC
Hi,

I am trying to figure out a way for a machine other than the SQL Server 2000 machine hosting my DTS jobs to fire off a DTS job on request. The jobs are scheduled, but I want to be able to fire them off remotely if the schedule fails. I don't have access Enterprise Manager on the second machine I want to start the jobs from.

An added restriction I have is that I can only execute command line shell scripts on the second machine. This could be a batch file, an executable or any other function available from the command line.

In an initial attempt I made the SQL Server Binn directory available via a restricted network share so the second machine could access the DTSRun.exe file. I then created the DTSRun commands and set them up to run. This worked fine, except I ran into another complication. The DTS Jobs I am running make use of an ActiveX DLL and an ActiveX Control (OCX). These files are registered on the server, but since using DTSRun forces execution to take place on the machine issuing the DTSRun command, the jobs fail because the components are not installed on the second machine.

I would like to try and aviod installing these components on the second machine and was hoping someone could suggest an alternative method for remotely fireing off these DTSJobs.

Any help would be greatly appreciated!

Thanks!
- Brad|||Hi Hugh, thanks for the reply.

The VBScript option sounds like it may be the way to go. Using a number of retries approach may not work in this case because we need to be able to manually fire off the jobs on request. Catch is, the people who will be firing off these jobs don't have access (or skills) to go into enterprise manager to run them.

If a VBScript file is used to fire off a job using ADO, would the job be executed on the server or on the local client machine? From your comments on context I gather it will be run on the server (which is a good thing in this case as it avoids the ActiveX components needing to be installed on client machines).

Thanks again for the help!

- Brad

[SIZE=1]Originally posted by hmscott
My first suggestion would be to use a VBScript that uses ADO to manually force execution of the remote job that runs the DTS package.|||Im using a stored procedure to run my DTS from the outside whenever I want.

CREATE PROCEDURE ProcImportFile AS
EXEC msdb.dbo.sp_start_job @.job_name = 'Import_file'
GO

I scheduled the DTS package to get a job to call from the procedure.

Haven't tried this solution fully yet beacuse there is some problems regarding the permissions on my database. If i execute the package manually it works fine. But the scheduled job fails averytime...
Helpdesk hasn't fixed it yet.