DTS Execution
-
Hello all,
I have been tasked with finding the best way to allow our users to execute certain DTS scripts. An example would be for our payroll process where the payroll specialist transfers files from the payroll system to the general ledger system etc. When the user needs to run the process they go to a webpage on our Intranet and select the script from a list. When the task is selected an entry is added to a table. A job runs every 5 minutes to see if any new entries have been added to the table. If there are any new entries then the job associated with the script is executed.
This works but seems a little hokey to me. I would like to have something that executes immediately and provides the user with some feedback. I'm thinking that our webpage should execute the DTS's directly without using an unscheduled job.
What ideas do you have?
Thanks,
Bryan
-
Bryan,
I would say that as long as you already have a web page that is used for submitting jobs then the most efficient solution would be to modify the page and have it exectute the task as opposed to scheduling it.
Of course, there may be extenuating events that require the job to be scheduled in the first place so I would check into that first.
Good Luck,
Darrell
-
The big problem you are going to run into by executing the DTS packages from the web page is that they will actually execute on the webserver itself. Also, you'll have to run the minefield of security applied in that execution context. Who is executing the package? What privelages do they need/have, etc..., etc...
You could change your checking job to execute every minute, or you could use the same sort of model that Reporting Services uses. A windows service that continually polls a given table and then triggers subsequent events.
--------------------
Colt 45 - the original point and click interface
-
Not sure if this is what you are looking for, but I have used the following stored proc create a job to execute a DTS Package on demand from a remote web server. The nice part about using a job is you can control the number of instances of the package that are executing. A job requires a unique name. This will also allow you to determine the userid that the job executes under.
--
-- Description:
-- Executes a DTS Package on Local server.
--
-- Author: Mike Gercevich
-- Date : 09/05/2004
--
--
CREATE PROCEDURE dbo.DTSPackage_Execute
(
@UserID varchar(25) = '',
@Password varchar(25) = '',
@PackageName varchar(100)= '',
@ReturnStatus Bit OUTPUT,
@PackagePrefixIdentifier varchar(100) = 'RunDTS-'
 
AS
SET NOCOUNT ON
DECLARE @rs INT
DECLARE @ErrorValue INT
SELECT @ReturnStatus = 0
IF LEN(@PackageName) > 0
BEGIN
DECLARE @job_id_out UNIQUEIDENTIFIER
DECLARE @ExecuteCommand varchar(1000)
DECLARE @newJobName varchar(200)
SELECT @ExecuteCommand = 'DTSRUN /S(local) /U' + @user-id + ' /P' + @Password + ' /N' + @PackageName
SELECT @newJobName = @PackagePrefixIdentifier + @PackageName
-- Create a new job
EXEC @rs = msdb..sp_add_job
@job_name = @newJobName,
@enabled = 1,
@description = 'Execute a DTS Package',
@start_step_id = 1,
@notify_level_eventlog = 3,
@delete_level = 3,
@job_id = @job_id_out OUTPUT
-- Check status
SELECT @ErrorValue = @@ERROR
IF (@RS <> 0) OR (@ErrorValue <> 0) GOTO ERR_HANDLER
-- Add a step to the job just created
EXEC @rs = msdb..sp_add_jobstep
@job_id = @job_id_out,
@step_id = 1,
@step_name = 'Start DTS Package',
@subsystem = 'CMDEXEC',
@command = @ExecuteCommand
-- Check status
SELECT @ErrorValue = @@ERROR
IF (@RS <> 0) OR (@ErrorValue <> 0) GOTO ERR_HANDLER
-- Add job server to the job
EXEC @rs = msdb..sp_add_jobserver @job_id = @job_id_out, @server_name = '(LOCAL)'
-- Check status
SELECT @ErrorValue = @@ERROR
IF (@RS <> 0) OR (@ErrorValue <> 0) GOTO ERR_HANDLER
-- run the the package now
EXEC @rs = msdb..sp_start_job @job_id = @job_id_out
-- Check status
SELECT @ErrorValue = @@ERROR
IF (@RS <> 0) OR (@ErrorValue <> 0) GOTO ERR_HANDLER
SELECT @ReturnStatus = 1
END
ERR_HANDLER:
IF (@RS <> 0) OR (@ErrorValue <> 0)
BEGIN
SELECT @ReturnStatus = 0
EXEC @rs = msdb..sp_delete_job @job_id = @job_id_out
END
RETURN @ReturnStatus
GO
-
Thanks all for the suggestions/info!
Mikes suggestion was most along the lines of what I was looking for.
Again thanks for all the input.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply