Dynamic Array Mapping in ADF Copy Activity

Question

I'm attempting to set up dynamic mapping between blob storage in an ADF (Azure Data Factory) Copy activity. In the mapping section, I'm trying to add an additional column using dynamic content from a set variable that is an array. However, I encounter the following error: "Expression of type: 'Array[Any]' does not match the field: 'value'. Expected type is 'String'."

I'm passing the set variable data as: ["123", "456", "789"]. When I execute the Copy activity, it stores the data in string format instead of as an array, resulting in the value: "["123", "456", "789"]".

Is it possible to achieve the desired array type with a Copy activity, or must the data be sent as a string?

Answer 1

In Azure Data Factory (ADF), when using a Copy activity, the mapping section expects the data types to match the schema defined for the sink. The error you are encountering, "Expression of type: 'Array[Any]' does not match the field: 'value'. Expected type is 'String'," indicates that the Copy activity is interpreting the array as a string rather than maintaining it as an array.

To resolve this issue, you need to ensure that the variable you are passing is correctly formatted and that the target schema in your sink can accept an array type. If the sink expects a string, you will need to convert the array to a string format (e.g., JSON string) before passing it.

If you want to maintain the array structure, you might need to consider using a Data Flow instead of a Copy activity, as Data Flows provide more flexibility for transforming and handling complex data types, including arrays.

---

References:

• Schema and data type mapping in copy activity
• Array functions in mapping data flow

Answer 2

Hi @Akhil

Greetings & Welcome to Microsoft Q&A forum! Thanks for posting your query!

Is it possible to achieve the desired array type with a Copy activity, or must the data be sent as a string?

While the Copy Activity itself doesn't directly support array data types in its mapping, you can achieve the desired array type by leveraging the json() function to convert the array to a JSON object. This JSON object can then be mapped to the target column.

Here is a breakdown of the steps involved:

Set Variable Activity: Create a variable to store your array: ["123", "456", "789"].

Copy Activity Mapping: Use the json() function to convert the array to a JSON object in the mapping expression. Map the resulting JSON object to the target column.

By using this approach, you can effectively store the array data in the target system as a JSON array.

For additional information, please refer: https://stackoverflow.com/questions/65348233/parsing-complex-json-in-azure-data-factory

I hope this information helps. Please do let us know if you have any further queries.

---

If this answers your query, do click `Accept Answer` and `Yes` for was this answer helpful. And, if you have any further query do let us know.