Integrating VFP and DTS 1

[finsys]

Hi there,

We are in teh process of toying with DTS to control a schedule of VFP programs. We need to know if the VFP app has run succesfully or not (as opposed to crashing) so we built a small test app that you can click OK or Cancel on a messagebox and the app closes with a RETURN 1 or RETURN 0

In DTS, we should then be able to pick this return value up and conditionally move on to different routes of the DTS package. However... DTS doesn't seem to be seeing anydifference in return value. As far as it's concerened the VFP app is always returning 0!

Any ideas on where were going wrong?

Many thanks
Finsys.

 

[baltman]

I'm sure I'm being dense, but do you mean DTS Software, Digital Theater Systems (which would be cool ), or something else?

It might help if you posted some code/methodology.

Brian

 

[finsys]

Hi Brian, yes DTS as in SQL Server Data Transformation Services.

The code is pretty much as described above!

if messagebox("SUCCESS?",1)=1
    return 1
else
    return 0
endif

Either DTS is not picking up the return value from VFP, or VFP is not returning it properly in the first place. I might have to cross post this in the SQL forum, but since I don't know which is to blame (DTS orVFP) I thought I'd start here...

 

[Mike Lewis]

Finsys,

How are you calling the VFP code within DTS? If you are running it as an EXE, you are out of luck. Your code won't return a value to DTS. RETURN only returns a value to the calling program or method within VFP.

Have you thought of doing things the other way round: Let the VFP code call DTS? That will give you much greater control over the communication process.

Mike


Mike Lewis
Edinburgh, Scotland

My Visual Foxpro web site:

http://www.ml-consult.demon.co.uk

My Crystal Reports web site:

http://www.ml-crystal.com

 

[finsys]

Mike, yes it's an exe. We already have a gaggle of batch processing VFP programs that are controlled by a VFP scheduler that we wrote. We're having some problems with it due to network issues, so want to move as much as we could to the server using DTS.

Is there no way to make a VFP exe return a value? It would be nice and simple if we could just add return values to our existing flotila of apps.

 

[Mike Lewis]

Finsys,

Is there no way to make a VFP exe return a value?

I don't see how any EXE can return a value. The nearest you can get to that is to return an ERRORLEVEL, but I don't think VFP (or any other Windows program) can do that.

The only other possibility I can think of is to have the EXE create a small text file, or maybe a registry entry, that holds the value, but I don't know if DTS would be able to pick that up.

Mike


Mike Lewis
Edinburgh, Scotland

My Visual Foxpro web site:

http://www.ml-consult.demon.co.uk

My Crystal Reports web site:

http://www.ml-crystal.com

 

[finsys]

I don't see how any exe can not be made to return a value!

DTS provides for checking return codes from a win32 app to allow a descision making mechanism for the DTS package if a process fails to execute properly. This to me says that somewhere somehow you can get an exe to return a value. I shall keep hunting, I know it's out there somewhere!

 

[finsys]

I knew it could be done!

It's a simple API call...

DECLARE ExitProcess in Win32API ;
INTEGER ExitCode

IF MESSAGEBOX("Run successfully?",1)=1
	WAIT WINDOW "Success" TIMEOUT 2
	= ExitProcess (1)
	RETURN
ELSE
	WAIT WINDOW "Failed" TIMEOUT 2
	= ExitProcess (0)
	RETURN
ENDIF

DTS picks up the code returned through the API call perfectly.

I would assume this should work in any exe built from any language that can access the Windows API.

How do I give myself a star? LOL

 

[Mike Lewis]

Finsys,

Delighted that you found a solution.

I think the value returned via ExitProcess is the ERRORLEVEL number I mentioned earlier. It's fortunate that DTS recognises this value. Most modern Windows applications wouldn't know what to do with it.

But the main thing is that it works.

Mike


Mike Lewis
Edinburgh, Scotland

My Visual Foxpro web site:

http://www.ml-consult.demon.co.uk

My Crystal Reports web site:

http://www.ml-crystal.com

 

[finsys]

Been doing some further research on this. It seems one term for this is an Exit Code, I've also found it referenced as ErrorLevel as you said Mike.

"Each process in DOS / Windows returns an integer ExitCode to notify its parent process of the success / failure / other status upon termination. These aren't frequently used anymore, but they are sometimes very useful."

I would guess most programs are not written to use this. Most indeed wouldn't know what to do with them, as for the most part, most programs probably don't care. Something like NT Scheduler though can pick them up and uses them. I even found some code that you can use in VFP to monitor other processes that it has called and to wait for it to end and detect the ExitCode when it does terminate.

My code above is actually slightly wrong. There is no need for the RETURN statement as EXITPROCESS closes the app down. So in a proper piece of code I guess I'll need to set a variable with an error level value and then pass that back using the EXITPROCESS statement as the very last line of code in the program.

Thanks for steering me in the right direction!

 

[Mike Lewis]

Finsys,

You've done a good job on researching this. No doubt others will find it useful.

Mike


Mike Lewis
Edinburgh, Scotland

My Visual Foxpro web site:

http://www.ml-consult.demon.co.uk

My Crystal Reports web site:

http://www.ml-crystal.com