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-'

    &nbsp

    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