Results 1 to 3 of 3
  1. #1
    Join Date
    Jun 2002
    Location
    Singapore
    Posts
    71

    Unanswered: Problem With DTS Package - To Delay Process

    Hello All SQL Experts.

    Need your advise on this. I have a DTS package which check for 2 dates and execute tasks when the date do not matched. The problem I am facing now is I could make the next step to start only if the previous step is completed. When the DTS package is executed, all steps being completed almost at the same time. See below / attached DTS package.

    In the disgram, I have labelled 5 steps A ~ E, each step needs info from the finished product from previous step to produce correct result in it's own step. I couldn't schedule each step to run at different time because the DTS kicks off based on a file that comes in and each step doesn't have a fixed processing time to complete.

    I have tried using On Success or On Complete and both options start the next step immediately not not wait for the job the complete or success. I guess this is because I have transferred the command to external when using command. Is there a way to control by some delay between each task?

    Please advise. Thank you.

    Each of the step has something like below (refreshing of excel file with macro build in):- I cannot build all macros into one file and run from the main excel.

    declare @MainUpdate datetime
    declare @TempUpdate datetime

    select @MainUpdate=Main_Update_CET from APMEAPV_Compare
    select @TempUpdate=Temp_Update_CET from APMEAPV_Compare
    --select @MainUpdate, @TempUpdate

    if @MainUpdate<>@TempUpdate
    begin
    DECLARE @commandK varchar(1000)
    SET @commandK='Start Excel.exe "D:\Daily_Status_Report_EDWH\EDWH_Runbook_BTS.xls" '
    exec master..xp_cmdshell @commandK, No_Output

    END
    Attached Thumbnails Attached Thumbnails DTS_Package.bmp  

  2. #2
    Join Date
    Jan 2005
    Location
    Green Bay
    Posts
    201
    If you don't have the ability to crate a JOB on the server that will run steps
    and create each step as a different DTS or executable command.

    You can do a couple of things technically.

    Put a step in the DTS to loop a number 36000 = 1 second I think.

    Or You could use ACCESS (UGH) to create a virtual DTS and control each step from there You would need to make each step in the DTS a seperate DTS to do this.
    opackage.LoadFromSQLServer Server, , , DTSSQLStgFlag_UseTrustedConnection, "", "", "", DTSPackage, 0

    opackage.Execute

    opackage.UnInitialize

    Set opackage = Nothing


    I would ask what are you trying to accomplish and is this the best tool to do this.

    Hope this helps.

  3. #3
    Join Date
    Jun 2002
    Location
    Singapore
    Posts
    71
    Hello rbackmann.

    Thank you for your advise. I have introduced a count in each step to delay the start and they worked ok for me.

    Thank you.

Posting Permissions

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