Search code examples
azure-data-factoryazure-mapping-data-flow

azure adf mapping data flow expression with case statement on cached sink

I am using Azure Data Factory Mapping Data Flows.

I am trying to write an equivalent of sql 'case' statement to get a value in a derived column based on cashed sink.

My cashed sink input is a query: 'select ID from dbo.aaa'. It returns all ID as Integers, they are visible in 'Data Preview'.

My derived column expression is:

iif(contains(chachedIDs#, #item==ID), "upserted row", "deleted row").

The above must be wrong as I cannot preview, ADF says: 'Type mismatch'. 'Expression type could not be evaluated, correct the expression' enter image description here

Solution

  • To use sink cache in dataflow expression, you need to use Cached lookup functions.

    Use the below expression in the derived column to achieve your requirement.

    iif(contains(sink1#outputs(),#item.ID== ID),'upserted row','deleted row')

    My cached lookup output:

    enter image description here

    Use the above expression and this is my result.

    enter image description here

    While transforming these data to any other sink file, make sure you give correct sink ordering in settings.

    enter image description here