According to the documentation it isn't possible the way you want to use it.
In order to get the desired result I think you should change the approach a little.
At the end of the session you can pass the mapping variable to the workflow variable again.You can use the same mapping in a second session in which you can override the source filter
with: 1=2 (so no data ingested) pass the workflow variable on to the session and perform the SQL in the second session.
I'm not an expert on variables so hopefully someone else has a better suggestion.
Unfortunately I have to second JanLeendert's advice. Mapping variables are updated (not only in the repository but also for following steps) only after all steps in the session have been processed. All of them.
Which means that the value used for the post-session SQL is still the initial value of that mapping variable.
There's no (direct) way around that, this "works as designed" (that's the term used by Informatica support engineers when they find out that some seemingly strange behaviour has been implemented correctly according to internal requirements documentation).
So JanLeendert's suggestion is the safe way to go.Regards,Nico
Nico and JanLeendert,
Thanks for the responses. Well that's a bummer.
I can see the correct value being assigned to the $$TotalRecords map variable during the debugger run and yeah, I have a subsequent link between sessions that use that value also, and that works fine also.
The Post SQL Expression Editor lets you choose mapping variables to use in the SQL statement... why would they be there if they aren't the final value?
All, I'm going to rollback to my prior version and go with adding in the target to the map and do the INSERT inside the map.
I think that is the best idea.
You keep it visible in the mapping what happens and that's in my humble opinion where the logic should be. Especially when another developer needs to figure out what happens where.
Once I have seen a SQL-query in a source qualifier simply to join 2 tables and name the fields needed.
In the session the same developer did an override of the SQL just to add a join condition and a filter condition.
I really wondered why this developer didn't simply add a user defined join in the source qualifier and a source filter.