Results 1 to 9 of 9
  1. #1
    Join Date
    Mar 2004
    Location
    Leiria Portugal
    Posts
    14

    Unanswered: DTS and process task - doesn't wait my called exe application finish completely

    Hi,

    I need to execute a DTS that have a couple of steps and one of them is a process task that simply call an exe file i made that will send an email to warn the user.

    What happens here is that the process task executes my exe file but it doesn't wait for it to compete and fires the next task after and finally closes.

    There is anyway to make a "while statament" to wait until my exe application finishes?

    Any idea?

    Thanks in advance,
    Tiago Teixeira

  2. #2
    Join Date
    Jan 2003
    Location
    Nottinghamshire, UK
    Posts
    364
    I Don't think there is a way to reply to SQL that an executable that it started has now stopped running.

    Shame your not using the SQL email service but your prob doing other things as well.

    Do you have any contriol over the .exe source code ?

    Idea 1. it could possibly create a file in Dos then delete it as a last action before ending. TSQL would then loop (possibly with a while loop) to check if the file still exists notifying you that the .exe has done it's job.

    GW
    "Everything should be made as simple as possible, but not simpler." - Albert Einstein
    "Everything should be made as complex as possible, so I look Cleverer." - Application Developer

  3. #3
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Why not connect your steps with "On success" workflow/execution paths?
    Also, why are you calling another application to send an e-mail when the DTS package designer can send e-mails for you?
    George
    Home | Blog

  4. #4
    Join Date
    Mar 2004
    Location
    Leiria Portugal
    Posts
    14
    Quote Originally Posted by GWilliy
    I Don't think there is a way to reply to SQL that an executable that it started has now stopped running.

    Shame your not using the SQL email service but your prob doing other things as well.

    Do you have any contriol over the .exe source code ?

    Idea 1. it could possibly create a file in Dos then delete it as a last action before ending. TSQL would then loop (possibly with a while loop) to check if the file still exists notifying you that the .exe has done it's job.

    GW
    Hi,

    Thanks for the help.

    I don't use SQL email service because i don't have a MAPI server available to do it, and i don't want create it in IIS just for this purpose.
    I found a free dll extension around that sends email without a MAPI account but, the problem is that the DTS aim is export data to excel file and after send this excel file by email, the problem is that when i send the email ( using that dll) an error occurs because the excel file still locked by the precedent task, so i created myself an exe file that frees the xls file and send the email using a .NET library instead of the dll i said, but the erro i reported appears.

    About your second idea, can you please be more specific or paste a code snippet abou how to do that Loop. I would appreciate very much.

    Thanks in adavance,
    Tiago Teixeira

  5. #5
    Join Date
    Jan 2003
    Location
    Nottinghamshire, UK
    Posts
    364
    Hi Teix

    I was basically just thinking of something like this

    Code:
    
    DECLARE @cnt int
    WHILE(@cnt <= 5)
    BEGIN
    DECLARE @result int
    EXEC @result =xp_cmdshell'del Myfile.exe'
    IF(@result = 0)
    PRINT'Success'
    ELSE
    PRINT'Failure'
    WAITFORDELAY'00:15'
    SET @cnt = @Cnt +1 
    END
    
    Not sure how wise this approach is though

    Good Luck

    GW
    "Everything should be made as simple as possible, but not simpler." - Albert Einstein
    "Everything should be made as complex as possible, so I look Cleverer." - Application Developer

  6. #6
    Join Date
    Mar 2004
    Location
    Leiria Portugal
    Posts
    14
    Hi GWilliy,

    I found a way to manage it without code, i tried the code snippet you generously wrote but application behaved in the same way as before.

    So, i created by hand a schedule job and inspite of make a single DTS package that would make all the stuff i needed i sliced the main job in 3 parts and i added each one sequentially to the Job and after that all the steps were accomplished correctly and i got the stuff working.

    Thanks all for the precious help and ideas,
    Best regards,
    Tiago Teixeira

  7. #7
    Join Date
    May 2007
    Posts
    23
    Try windows handler dll, I think every exe program has unique windows handler id, use this id in a loop.... Just an Idea...

  8. #8
    Join Date
    Mar 2004
    Location
    Leiria Portugal
    Posts
    14
    thanks i'll investigate that later, but from now i think i've the job done.

    thanks
    Tiago Teixeira

  9. #9
    Join Date
    Jan 2003
    Location
    Nottinghamshire, UK
    Posts
    364
    Poison Ref
    Try windows handler dll,
    how would you access this from TSQL ?

    may be handy for future ref

    Teix - Glad U got sorted

    GW
    "Everything should be made as simple as possible, but not simpler." - Albert Einstein
    "Everything should be made as complex as possible, so I look Cleverer." - Application Developer

Posting Permissions

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