azure-data-factoryoracle-adfchange-data-captureazure-mapping-data-flow

use array as parameter in mapping Data Flow in Azure Data Factory

I am working with Change Data Capture (CDC) feature in mapping Data Flows in ADF. The feature picks up all new, deleted, or amended rows in the source table as expected. Nevertheless, it provides no indication what exactly happened to returned row: whether it was deleted, is it a new one, or it was amended. To understand what happened to particular row, I am hoping to use the below 'if - then' logic:

  1. if the row returned by CDC no longer exist in the source dataset - it is a deleted row.
  2. if the row exists in the source dataset - it is a new or an updated row.

I am using 'look up' activity before executing the dataflow to gather all the Primary keys (PKs) in the table. enter image description here This activity returns the list of all PKs, and I am parsing them as an array to my df_parameter.

This parameter is also defined within the Data Flow as an array of integers (my PKs are integers).

Inside the Data Flow I am using "derived column" activity with following expression: enter image description here Unfortunately, this does not work and returns Data Flow activity error: "Job failed due to reason: com.microsoft.dataflow.broker.InvalidParameterTypeException: Invalid type for parameter: df_parameter" Any help would be very appreciated

Solution

  • I tried your scenario and got same error.

    enter image description here

    This error occurred because, you are passing the Lookup output array to an integer array parameter of Dataflow.

    Here, Lookup output will be always an array of objects like below.

    enter image description here

    You are passing this to an Array of integer parameter in the dataflow and that's why it is causing the Invalid type error.

    To resolve it, first convert the Lookup output array of object into an integer array in the ADF using ForEach.

    First create an empty array variable in the ADF, here my array name is arr. Pass the Lookup output array to the ForEach and take an append variable activity inside that ForEach. Give the @item().ID in that and this will append the integer values to the array.

    enter image description here

    After ForEach, pass this array to the Dataflow integer array parameter.

    enter image description here

    Execute the pipeline, and you can see the integer array we are passing in the input of the dataflow activity.

    enter image description here

    My Result:

    enter image description here