EN VI

Azure - Write source dataset parameters to sink in ADF copy data activity?

How to Azure - Write source dataset parameters to sink in ADF copy data activity

I'm using ADF to ingest data from a REST API into a SQL database. One endpoint contains dates of public holidays and has a 'year' parameter that must be specified in order to extract the holidays for that year. I'm using ADF's built-in pagination to loop over multiple years in a single Copy Data activity.

I'd like to write the 'year' value to the SQL sink when I do this. This is my current setup:

The API output looks like this:

[
  {
    "id": 1,
    "name": "New Year's Day",
    "region": 1,
    "dates": [
      "2021-01-01"
    ]
  }
]

I've tried to use Additional Columns to pass the year value to the sink, but it's not currently working. How can I setup my activity to write this value to the sink?

Solution:

As per MS doc It only take static values, variables parameters and values from upstream activity's output

Add a column with ADF expression, to attach ADF system variables like pipeline name/pipeline ID, or store other dynamic value from upstream activity's output.

Add a column with static value to meet your downstream consumption need.

As you want the year value at runtime, so you need to use data flow wherein you can create derived columns and manipulate the initial column value, But the Range function is not supported there.

So, the possible workaround is using dataflow after copy activity:

Here first you need to copy data from Rest Api to CSV file.
Then take the Dataflow add above created file in dataflow as source based on that source create the derived column on it and extract value of year using year() function.

enter image description here