POST Processing not completed before moving on in the Workflow

[Roan Linde]

Hi there AWA Community

I'm running into an issue where we try and save the value from the REP report and put it in a VARA object during POST PROCESSING. The problem is that the saved value only shows up AFTER the workflow has moved on - so essentially, it's always one iteration behind.

The POST Process Script looks like this:

:SET &OUTVARA# = "VARA.SQL.VARIABLE"
:SET &COUNTER# = 0
:SET &HND_PP# = PREP_PROCESS_REPORT(,,'REP')
:PROCESS &HND_PP#
: SET &LINE#=GET_PROCESS_LINE(&HND_PP#)
: SET &COUNTER# = ADD(&COUNTER#,1)
: IF &COUNTER# = 137
: PRINT "****** TOTAL VALUE &LINE#"
: PSET &VAL3# = &LINE#
: ENDIF
:ENDPROCESS
:CLOSE_PROCESS &HND_PP#
:PRINT &VAL3#
:PUT_VAR &OUTVARA#, "TOTAL", &VAL3#

So if I run the workflow the first time, it fails since the VARA is only populated AFTER the second job has kicked off. If I run it a second time, it successfully completes, but with the value of the first iteration. So it's always behind. 

I can't really do this with a SQL VAR since the ID I need to generate the SQL Select statement is only created during the PROCESS portion - so I need to wait for the REP report to be available in order to grab the ID, set it as a VARA and read it with the next JOBS in the workflow. 

I've tried both "Activation- & "Runtime" task generations. 

Is it normal behavior for POST to not finish before moving on to the next step? Any way I can force it? 

Thanks in advance

[Roan Linde]

Just a note - was able to sort this out. I was not aware that even the workflow needs to be set to generate tasks at runtime. So very much in line with what @Peter Grundler suggested. ​

[Peter Grundler]

Hi Roan,

please check the "generate at" setting of the successor tasks. I suppose that they are using GET_VAR to retrieve the VARA content?

regards,
Peter

[Roan Linde]

Hi there @Peter Grundler

Sorry, I see I can't reply since it's not an open-ended thread. 

So all the JOBS in the workflow is set to generate at activation. Although, I have tried Runtime without luck.
It just doesn't make much sense to not complete all the steps of JOB 1 before moving on to JOB 2. As far as I can tell - doing it in POST is the recommended way of getting SQL returns. ​

And yes - the next SQL job in the workflow run the following in PROCESS:

:PSET &TEST# = GET_VAR(VARA.SQL.VARIABLE, "TOTAL")
:PRINT "************* &TEST#"
SELECT [id] FROM [ssis_automic_test].[dbo].[MOCK_DATA] where [id] = &TEST#;

So the first iteration it would return a blank for &TEST#:

2022-02-02 07:53:12 - U00020206 Variable '&TEST#' was stored with value ' '.
2022-02-02 07:53:12 - U00020206 Variable '&VAR2#' was stored with value '14'.

And the second time around it would return it with the value set in interation 1:

2022-02-02 07:54:32 - U00020206 Variable '&TEST#' was stored with value '6'.
2022-02-02 07:54:32 - U00020206 Variable '&VAR2#' was stored with value '14'.



But if look at the activation times of the JOBS, everything is starting at the same time - which again I don't think is correct since there is a logical flow + dependency on the preceding tasks to end OK - there is even post-conditions on some of them.