I there a way to fail the process flow based on a condition.

Eg. If process flow contain D1,D2,D3 data flows running in a sequence. I would like to fail the process after D1 based on a condition.
Fail if &&FLAG = 'FALSE' or Continue to next flow if &&FLAG = 'TRUE'

This message has been edited. Last edited by: <Kathryn Henning>,

Yes, I have done this, but you will probably need to use a data migrator stored procedure to make the determination for the value for the global parameter. The newer D.M. has the SET VARIABLES option now, and I haven't explored it that much yet as we've only just converted from the old 7.6 Data Migrator - but it looks like it can query only system/statistical variables - but you might check out the documentation on it to see if it can do more than that. If not, and what I have used successfully, is to write a D.M. stored procedure to read a given file that holds the resultant condition to be checked. Then, depending on the value returned, it sets a global (&&) variable which can be queried in the flow connecting line.

For example - below is a stored proc that reads a control table we created. This file has records for each table that we load during an ETL process. It indicates whether the given table was loaded successfully or not. The stored proc simply reads it to count how many were successfully loaded and how many failed based on the particular table grouping that was passed into it; the proc uses that information to determine the value to which to set the global variable &&LOAD_RESULT.

In your process flow, you have the stored proc 'box' that represents the below code and out of it can come your flow paths. I have three for this one to send it down the desired path, which could include just stopping after the condition has been met (or stopping after sending appropriate alerts, etc.):

First flow path - custom condition checked:
(&FOCERRNUM EQ 0 AND &&LOAD_RESULT EQ 'OK')

Second flow path - custom condition checked:
(&FOCERRNUM EQ 0 AND &&LOAD_RESULT EQ 'NOTOK')

Third flow path - failed condition checked:
(&FOCERRNUM NE 0)

Here is a sample D.M. stored procedure (If you are not familiar with D.M. stored procs, they look a lot like a WF .fex and it is fact a .fex type of program):
=======================================================================================

-**************************************************************************************
-* PROC_TRODS_PROCESS_DETERMINE_IF_ALL_LOADED
-*
-* This procedure will read the TRODS_CTL_TABLE_LOAD_CONTROL file and determine if
-* all the tables of the designated (via input parm) PROCESS_TYPE and SOURCE_SYSTEM
-* were loaded successfully or not. If all loaded successfully
-* it will set the global variable "&&LOAD_RESULT" to "OK"; if not it will set it to "NOTOK"
-*
-*
-**************************************************************************************


-TYPE *******************************************************************************
-TYPE *******************************************************************************


-***********Variable Initialization***********
-SET &THISPROCNAME='PROC_TRODS_DETERMINE_IF_ALL_LOADED';

-SET &&NBR_LOADED_OK = 0;
-SET &&NBR_NOT_LOADED_OK = 0;
-SET &&LOAD_RESULT = 'UNKNOWN';

-SET &&DBCONNECTIONTGT='WFRARG_RW';
-SET &&DBENGINETGT='SQLMSS';


-TYPE "&THISPROCNAME" started

-*********************************************************************
-* OBTAIN THE NUMBER SUCCESSFULLY LOADED
-**********************************************************************
ENGINE SQLMSS SET DEFAULT_CONNECTION WFRARG_RW
-RUN
-SET MESSAGE=OFF;
SQL SQLMSS

SELECT COUNT(*)
FROM TRODS_CTL_TABLE_LOAD_CONTROL
WHERE
SOURCE_SYSTEM = '&SOURCE_SYSTEM' AND
PROCESS_TYPE = '&PROCESS_TYPE' AND
TABLE_STATUS = 'SUCCESSFUL' ;

TABLE ON TABLE SAVE AS SQLOUT00
END
-RUN
-SET MESSAGE=ON;
SET NODATA = ' '

-READ SQLOUT00 &TABCOUNT.A11
-RUN
-TYPE READ FOR SUCCESSFUL STATUS:
-TYPE IORETURN : &IORETURN
-TYPE NBR LOADED OK: &TABCOUNT
-IF &IORETURN NE 0 THEN GOTO :THE_END;

-SET &TABCOUNT=STRIP(11, &TABCOUNT, ' ', 'A11V');
-SET &TABCOUNT=TRUNCATE(&TABCOUNT);

-SET &&NBR_LOADED_OK = &TABCOUNT;


-*********************************************************************
-* OBTAIN THE NUMBER NOT SUCCESSFULLY LOADED
-**********************************************************************
ENGINE SQLMSS SET DEFAULT_CONNECTION WFRARG_RW
-RUN
-SET MESSAGE=OFF;
SQL SQLMSS

SELECT COUNT(*)
FROM TRODS_CTL_TABLE_LOAD_CONTROL
WHERE
SOURCE_SYSTEM = '&SOURCE_SYSTEM' AND
PROCESS_TYPE = '&PROCESS_TYPE' AND
TABLE_STATUS <> 'SUCCESSFUL' ;

TABLE ON TABLE SAVE AS SQLOUT00
END
-RUN
-SET MESSAGE=ON;
SET NODATA = ' '

-READ SQLOUT00 &TABCOUNT.A11
-RUN
-TYPE READ FOR NOT SUCCESSFUL STATUS
-TYPE IORETURN : &IORETURN
-TYPE NBR NOT LOADED OK: &TABCOUNT

-IF &IORETURN NE 0 THEN GOTO :THE_END;

-SET &TABCOUNT=STRIP(11, &TABCOUNT, ' ', 'A11V');
-SET &TABCOUNT=TRUNCATE(&TABCOUNT);

-SET &&NBR_NOT_LOADED_OK = &TABCOUNT;


-:THE_END
-TYPE *******************************************************************************

-SET &&LOAD_RESULT = IF &&NBR_NOT_LOADED_OK GT 0 OR &&NBR_LOADED_OK = 0 THEN 'NOTOK' ELSE 'OK';

-TYPE PROCESS TYPE.................: &PROCESS_TYPE
-TYPE NBR_NOT_LOADED_SUCCESSFULLY..: &&NBR_NOT_LOADED_OK
-TYPE NBR_LOADED_SUCCESSFULLY......: &&NBR_LOADED_OK
-TYPE LOAD_RESULT..................: &&LOAD_RESULT
-TYPE FOCERRNUM....................: &FOCERRNUM


-TYPE "&THISPROCNAME" completed
-TYPE *******************************************************************************
-TYPE *******************************************************************************
-EXIT
=======================================================================================

We don't recommend editing the DataMigrator generated focexec. When you open it from the DMC such changes are lost.

Instead create a new stored procedure (focexec) with the line
-SET &&KILL_RPC='Y';
(For more information about this variable search for it in the User's Guide.)

Then add the stored procedure on the Process Flow tab using a condition to determine whether it gets executed or not.

Thanks Guru and Sharon for reponses,

-SET &&KILL_RPC='Y'

worked for me

Just as info, I did not say anything about modifying the generated foc exec program. I was indicating how to set a global variable based on a condition that needed to be determined. So if you needed logic to determine what value to put into the global variable, that can be done by creating a stored procedure program (by right clicking on the given application folder and selecting Stored Procedure). Once the stored procedure is done you would then call it from within a normal process flow - all maintained within the GUI.