Parameterise Dataverse tables using Azure Data Factory

Summary

Azure Data Factory has a great set of tools to manage data pipelines. One of those tools is the ability to create arrays and use the array properties as parameters. In the example below, I’ll demonstrate how to extract records from the Dataverse without having to create dozens of datasets and pipelines.

Data Factory Variables

Create a new variable of type array in a new Pipeline.

This array represents the list of tables that we’ll iterate over.

As we are iterating over this collection, we’ll need to use the ForEach activity. Add the ForEach activity and pass the variable that was created in the previous step. To access the property of a ForEach activity, the @item() expression must be used.

Use @item() to iterate over a single enumeration in ForEach activity. For example, if items is an array: [1, 2, 3], @item() returns 1 in the first iteration, 2 in the second iteration, and 3 in the third iteration. You can also use @range(0,10) like expression to iterate ten times starting with 0 ending with 9.

Now, in order to access the properties of the array in any other ADF Activity, add the ForEach Iterator expression. This should add the @item() expression automatically.

In the screenshot below, I am adding the item iterator to a SQL table value.

In this instance, clicking on the ForEach iterator will populate the value field with @item().

That’s all that is required.