DTS and process task - doesnt 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 TeixeiraI 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|||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? :D|||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|||Hi Teix

I was basically just thinking of something like this


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|||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|||Try windows handler dll, I think every exe program has unique windows handler id, use this id in a loop... Just an Idea...|||thanks i'll investigate that later, but from now i think i've the job done.

thanks
Tiago Teixeira|||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