Informatica PC: how do I make a decision in the flow upon scalar query result?

Question

I'm struggling with what seems like the simplest thing there is: assigning a value to a mapping variable that I later on use in my flow to make a decision upon... With my MS SSIS background this is a 10 seconds task, however in Informatica PowerCenter, it is taking me hours...

So I have a mapping variable $$V_FF and a workflow variable $$V_FF. At first the names were different but while trying things out, I changed that. But that shouldn't matter, right?

In a mapping, I have a view as a source that returns -1, 0 or 1. The mapping variable aggregate function is set to MIN.

In the session that I have created for this mapping, I have a post-session assignment between the wf variable and the mapping variable. In this mapping I use setvariable function in an Extrans block.

Every time I run the wf, I see in the log that it uses a persistent value instead of assigning a new value everytime the flow is running...

What am I missing here?

Thanks in advance!

Answer 1

In the end, I managed to accomplish what I wanted back then. There might be a better way, but this solution is easy to maintain and easy to understand.

1. Create a variable in your workflow, lets say $$FailureFlag with type integer.
2. Create a view in your DB that returns 1 row with a integer value between 0 and x, where x is a positive integer value.
3. Create a mapping with the view that we just created as the source and use a dummy table as destination.
4. In this mapping you also create a variable, lets say $$MYVAR, with type integer and aggregation "Count". In an "Expression Transformation" I assign the result of the view, column FF, to this variable $$MYVAR by using SETVARIABLE($$MYVAR,FF).
5. From this mapping, create a session in your workflow. In the Components tab, in the "Postsession_success_variable_mapping" section, add a row and link workflow variable $$FailureFlag with session variable $$MYVAR.
6. Add a Decision component right after the session you just created, and test the content of your workflow variable, for example $$V_FAILURE_FLAG_IMX = 1.
7. Connect your decision then with your destination and add a test clause for example: "$MyDecision.Condition = true AND $MyDecision.PrevTaskStatus = succeeded"

Voila, that's it.