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

Question

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:

if the row returned by CDC no longer exist in the source dataset - it is a deleted row.
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. 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: 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

score 1 · Accepted Answer · answered Jul 29 '23 at 15:19

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

thank you @Rakesh, most sincerely appreciate your help and detailed explanation — Andrii Rozrutskyi, Aug 01 '23 at 10:53
the above works for arrays that are smaller than 5000 items. This seems to be a maximum number of elements in the array for the preceding 'for each' append variable function.... — Andrii Rozrutskyi, Aug 07 '23 at 10:37
Lookup limitation is only 4MB that means 5000 rows or items. — Rakesh Govindula, Aug 07 '23 at 10:53

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

1 Answers1