Copy activity dynamically populated additional columns error when using Expression values

[patto_uon]

I'm trying to build out a metadata driven pipeline using copy activities. Everything works fine when I attempt to pass in a dynamically generated array for the set of additional columns, such as:

{
  ...
  "additional_columns": [
    {
      "name": "new_column_name",
      "value": "demo"
    }
  ]
  ...
}

However, when I attempt to pass through an Expression (as defined here) :

{
  ...
  "additional_columns": [
    {
      "name": "dsp_last_modified",
      "value": {
        "value": "@formatDateTime(utcNow())",
        "type": "Expression"
      }
    }
  ],
  ...
}

 I get the following error message:

Operation on target Copy data to Delta table failed: ErrorCode=UserErrorInvalidValueInPayload,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=Failed to convert the value in 'value' property to 'System.String' type. Please make sure the payload structure and value are correct.,Source=Microsoft.DataTransfer.DataContracts,''Type=System.InvalidCastException,Message=Object must implement IConvertible.,Source=mscorlib,''

I've validated that the dynamic parameter generates exactly the same input as when I hardcode the expression into the activity via the UI. Not sure if I am missing something obvious here?

[v-tsaipranay]

Hi @patto_uon ,

We haven’t received an update from you in some time. Could you please let us know if the issue has been resolved?
If you still require support, please let us know, we are happy to assist you.

Thank you.

[v-tsaipranay · Accepted solution]

Hi @patto_uon ,

Thanks for the clarification. To confirm, expressions such as @{formatDateTime(utcNow())} are only evaluated when they are authored directly inside the pipeline. When the same string comes from external metadata, Fabric treats it as normal text, which is why you see the expression printed instead of the evaluated value.

So when I mentioned a “pipeline-ready expression format,” it meant that your metadata should contain the information needed to build a valid expression, but the pipeline still needs to convert that metadata into an actual expression before passing it to the Copy activity. Currently, the service cannot execute expressions coming directly from metadata files.

To achieve a metadata-driven approach, a small preprocessing step in the pipeline is required to translate metadata into real expressions.

Thank you.

[v-tsaipranay]

Hi @patto_uon ,

We haven’t received an update from you in some time. Could you please let us know if the issue has been resolved?
If you still require support, please let us know, we are happy to assist you.

Thank you.

[patto_uon]

Hi @v-tsaipranay ,

In your response below, you mentioned two available options to achieve this goal:

1. Pre-process the metadata before assigning it to the Copy activity and convert any "type": "Expression" entries into Fabric-compatible string expressions (for example, "@{formatDateTime(utcNow())}").
2. Update the metadata format so engineers store expressions directly in the pipeline-ready expression format rather than using the “typed expression” structure.

I am not quite sure I understand what you mean in option (2). What is a "pipeline-ready expression format"?

I assume this is not the interpolated string format "@{...}". As I mentioned in an earlier response, when I attempt to provide a metadata parameter in the following format:

[
  {
    "name": "dsp_last_modified",
    "value": "@{formatDateTime(utcNow())}"
  }
]​

 Then I see the expression string, rather than the interpolated value:

[v-tsaipranay]

Hi @patto_uon ,

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. 

Thank you.

[v-tsaipranay]

Hi @patto_uon ,

Thanks for the update. The behavior you’re seeing is expected.

The Copy Data activity currently does not support evaluating expression objects that use the "type": "Expression" structure when they are provided through external metadata. When this JSON is passed into the activity, it is treated as a standard object rather than a pipeline expression, which leads to the conversion error you are encountering.

In Fabric Data Factory, additional column values must be provided as string-based expressions in the @{...} format (for example, @{formatDateTime(utcNow())}), as these are the only expressions the activity can evaluate at runtime. This is why it works when authored directly in a Set Variable activity but fails when the same expression is nested inside metadata using "type": "Expression".

If you need to maintain a metadata-driven pattern, you have two options:

1. Pre-process the metadata before assigning it to the Copy activity and convert any "type": "Expression" entries into Fabric-compatible string expressions (for example, "@{formatDateTime(utcNow())}").
2. Update the metadata format so engineers store expressions directly in the pipeline-ready expression format rather than using the “typed expression” structure.

Currently, the service does not evaluate typed expressions coming from external JSON automatically, so one of the above approaches will be required to ensure compatibility with the Copy activity.

Thank you.

[v-tsaipranay]

Hi @patto_uon ,

Thank you for reaching out to the Microsoft fabric community forum.

Could you please let us know if the issue has been resolved? I wanted to check if you had the opportunity to review the information provided by @nielsvdc . If you still require support, please let us know, we are happy to assist you.

Thank you.

[patto_uon]

Hey @v-tsaipranay, no unfortunately I still have the same issue. 

• If I load metadata from a file that contains the "type": "Expression" terminology into a variable and then pass that into the copy activity, then I receive the original error message ("Failed to convert the value in 'value' property to 'System.String' type")
• If I write the expression directly into the set variable activity, it all works. However, that doesn't allow my engineers to write expressions in their metadata.

[nielsvdc]

Hi @patto_uon, are you using the [Set variable] activity for creating the additional columns json? If not, do the following.

• Create a pipeline variable named "AdditionalCols".
• Add a [Set variable] activity and link it to the "AdditionalCols" variable.
• Edit the dynamic content for the [Set variable] activity and add the additional columns json array as something like below.

  [
    {
      "name": "dsp_last_modified",
      "value": "@{formatDateTime(utcNow())}"
    },
    {
       "name": "run_id",
       "value": "@{pipeline().RunId}"
    }
  ]​

• In the [Copy data] activity assign the variable to the additional columns dynamics content and converting it to a json object.

  @json(variables('AdditionalCols'))​

Hope this helps. If so, please give a Kudos 👍 and mark as Accepted Solution ✔️.

[patto_uon]

Yes, I'm using a Set Variable activity for this particular field, and that is the workaround that I have used. 

However, I'm trying to make this a generic setup for data engineers to provide additional column definitions as part of the metadata included in JSON files, so this approach won't work for that scenario.

[nielsvdc]

Your initial json is from the pipeline json (with "type": "Expression"). When you want to generate a the pipeline json from metadata, you can generate the json in this format.

But if you want to input a dynamic json for additional columns in an existing pipeline, you should use the sample json code I showed you. When you edit a pipeline, have a look in the menu at View > Edit Json code. You will see the complete pipeline json code.

Hope this helps. If so, please give a Kudos 👍 and mark as Accepted Solution ✔️.

[patto_uon]

Hey @nielsvdc - thanks for the suggestion. Unfortunately, no luck - it just gets treated as a regular string:

[nielsvdc]

Hi @patto_uon, you might try and use string interpolation to dynamically generate the json. See the example below.

{
  ...
  "additional_columns": [
    {
      "name": "dsp_last_modified",
      "value": "@{formatDateTime(utcNow())}"
    }
  ],
  ...
}

More on this can be found here Expressions and functions - Microsoft Fabric | Microsoft Learn

Hope this helps. If so, please give a Kudos 👍 and mark as Accepted Solution ✔️.