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

DTS - SQL Task question

Hi,

I have a DTS package. One of the steps is SQL task step. I have the following code under SQL statment.

declare
@.p_dtProcessDate DateTime,
@.p_iResultCode int
SET @.p_dtProcessDate = getdate()
EXEC SPCreateMLRDailyRpt @.p_dtProcessDate,@.p_iResultCode out

I want to evaluate @.p_iResultCode and if it is not equal to zero, I want to make this SQL task step to be failed so that the entire DTS package itself fails.

Does any one know how to do this ? I appreciate all your help.

-BheemNot that familure with DTS but how about RAISERROR?|||Paul,

Thanx for the response. But that does not fail the step. It says DTS package completed successfully.

-Bheem|||Using RAISERROR is the correct answer. Please post your code that did not function correctly using RAISERROR.|||A RAISERROR will trigger the 'On Failure' workflow. This was used in the flowing past post

Problem with package creation (http://dbforums.com/showthread.php?s=&threadid=314650)|||My code is below.

declare @.p_iResultCode int

EXEC spValidateCLLITemp @.p_iResultCode out
IF @.p_iResultCode = 0
RAISERROR('Failed',16,1)

When I run the same query using query analyzer, RAISERROR works fine with the message displayed as 'Failed'.

But in DTS the SQL task step is completed successfully without any problem. What am I doing wrong here ?

Thanx for all the responses.

-Bheem|||Have you tried the same code but leaving out the "if" statement ? Basically, forcing a RAISERROR.|||Yes, I did. It is not working either. Basically RAISERROR is not finctioning in DTS.

Does anyone know why this code is not working ?

-Bheem|||What version/service pack of sql server are you using ?|||I am using

SQL Server 7.0
Version 4.0
Service pack 6

If I use the following in DTS SQL task it works.

declare @.test int
set @.test = 0
if @.test = 0
RAISERROR('Failed', 16,1)

But RAISERROR is not working when I use EXEC <stored proc>
Thanx..
-Bheem|||I've created a small DTS package to test RAISERROR. I use a OLEDB connect that points to the local server and Pubs database using NT authentication. It starts with a SQL Task that get the milliseconds of the current time using DATEPART. If the value is less than 500 then RAISERROR and else do nothing. The SQL Task has 2 workflows ON SUCCESS and ON FAILURE, each with a message box. I've attached the DTS package see if you can run this.

I had to rename the extension from .DTS to .TXT so that I could attach|||achorozy,

The attachment is unreadable. Can you please paste your code here.

thanx..
-Bheemsen|||It's a DTS package that you can't read with notepad but import into DTS using Enterprise Manager.

Long form:

1) Create OLEDB connection to any database
2) Create SQL Task, set connection to OLEDB created in (1). Set SQL statement toif (SELECT DATEPART(ms,GETDATE())) < 500
raiserror('',16,1)
3) Create ActiveX Script called Good, code
Function Main()
Msgbox "Good"
Main = DTSTaskExecResult_Success
End Function
5) Set workflow 'ON SUCCESS' from SQL Task to ActiveX 'Good'
6) Create ActiveX Script called Bad, code
Function Main()
Msgbox "Bad"
Main = DTSTaskExecResult_Success
End Function
7) Set workflow 'ON FAILURE' from SQL Task to ActiveX 'Bad'

Run the package, it should flip from msgbox 'Good' to 'Bad' as the milliseconds in GETDATE() change from over 500 to under 500.|||achorozy,

Thanx for your response. Your code perfectly works. As I said, I had already tested RAISERROR with a SQL statment in the SQL task. That works.

But the RAISERROR is not working in my following code.

declare @.p_iResultCode int

EXEC spValidateCLLITemp @.p_iResultCode out
IF @.p_iResultCode = 0
RAISERROR('Failed',16,1)

When I run the same query using query analyzer, RAISERROR works fine with the message displayed as 'Failed'.

But in DTS the SQL task step says it is "completed successfully", it should fail actually. What am I doing wrong here ?

-Bheemsen|||Now I've changed my SQL Task.

I created a stored procedure :
CREATE PROC sp_Test (@.x int OUTPUT)
AS
SET NOCOUNT ON

SELECT @.x = DATEPART(ms,GETDATE())
Then I modified my SQL Task's SQL Statement to be
declare @.x int

exec sp_Test @.x OUTPUT

IF (@.x < 500)
raiserror('',16,1) This works fine for me. I get the RAISERROR sometimes (@.x < 500) and sometomes not (@.x >= 500).|||Are you still having the problem ?|||rnealejr,

Yes, I still have the problem. It is really weird. The same code works well in query analyzer, but not in DTS sql task step. It never fails there.

Your code is very perfect. It is wroking fine in DTS sql step as well as in query analyzer.

Looks like may be something wrong with my procedure. It is working fine when I write a small stored procedure and test the same return code in sql task step.

It is still a mystery for me.

Thanx for all responses. Please let me know if you find something for me. All the information regarding my problem is in this thread.

-Bheemsen|||There is a problem with the OLEDB provider for SQL. You need to download the latest mdac for your machine. If install version 2.6 or greater you should be ok. This should correct the non-failure in your dts script. If not, let me know.

Good luck.|||rnealejr,

I am sorry to ask this question. How do I check my OLEDB version and from where I should download the latest mdac ?

Thanx for all your help.
-Bheemsen|||Search for sqloledb.dll - right click on the file and go to properties - go to the version tab. Let me know which version you have installed.|||The OLEDB version installed is 07.01.0819

When I run "select @.@.version" in query analyzer, I got the following.

Microsoft SQL Server 7.00 - 7.00.842 (Intel X86)
Mar 2 2000 06:49:37
Copyright (c) 1988-1998 Microsoft Corporation
Desktop Edition on Windows NT 4.0 (Build 1381: Service Pack 6)

Thanx..
-Bheemsen|||Go to the following web site for mdac version 2.6:

mdac (http://www.microsoft.com/data/download_260rtm.htm)

After the installation, make a note as to which version is installed now. It should begin with 2000. You can also upgrade to 2.7 version.
FYI: There is a service pack for 2.6.

Good luck.|||rnealejr,

Thanx a million for all that help. It worked like a charm. It looks like this bug fix is incorporated by Microsoft in its recent SP4 also. I had installed SP4 too on my machine. I need to inform my production support people to install the same.

Thanx again.
-Bheemsen|||Good deal. Be careful, because not even sp4 has the latest drivers and your dts problem will still be around. You can test it out on your existing servers and let me know (or uninstall the sp4 on your machine).

Good luck.|||Thanx. I will. How did you figure out this fix ??

Also, when you have time, can you please take a look at my another question at thread "Delete query to remove duplicate rows"

-Bheemsen