Additional column that records the row number automatically in a copy activity in Azure Data Factory

Question

I have a Copy Activity in my ADF Pipeline which copies an excel worksheet data to a JSON sink. I am looking for a way to add a custom column that can keep a track of the row number while copying the data. For example,

Name Value
ABC 123
DEF 456
GHI 789
The above should be converted into the below JSON:

[{
"Name": "ABC",
"Value": "123",
"Row Number": 1
},
{
"Name": "DEF",
"Value": "456",
"Row Number": 2
},
{
"Name": "GHI",
"Value": "789",
"Row Number": 3
}]

Answer 1

Hi @Tinu Oommen ,

Welcome to Microsoft Q&A forum and thanks for your query.

Unfortunately Copy activity is intended to copy data among data stores located on-premises and in the cloud. After you copy the data, you can use other activities to further transform and analyze data. For your requirement Mapping data flow activity is better suited. Please see below steps to achieve your requirement using Mapping data flow.

1. Below is how source looks like:
2. Then add a Derived Column transformation with the ConstantValueColumn ConstantValue_1 and value set to 1 or any other constant value of your preference. Your Data preview looks like below
   
3. Then add a Window transformation with the rowNumber() function and ConstantValue_1as a sorting column.
   Data preview looks like below:
   
4. Then add a sink transofrmation and do map only the required columns Data Preview:
   

Hope this helps. Do let us know if you have further query.

----------

Please don’t forget to Accept Answer and Up-Vote wherever the information provided helps you, this can be beneficial to other community members.

Answer 2

Hi @Tinu Oommen

Apologies for the delayed response.

I did tried multiple ways and finally was able to come up with a workaround to get the desired output. You will need additional 3 transformations followed by Surrogate Key transformation to make the JSON hierarchy (array document). Please see below image.

Here is the GIF of implementation:

Please note that, in the sink transformation, I have used a delimited dataset but in the sink file name options I have provided a file name with .json extension (EXPRESSION USED : concat('CSV_Output_',toString(currentDate('PST'), 'yyyyMMdd'),'.json')) so that your output will save a JSON, also set the partition to single partition.

And also please note after creating the delimited dataset, you will have to click on Edit for both Escape character and Quote character and enter space, so that output will not have backslash and double quote characters.

Hope this helps. Do let us know if you have further query.

Answer 3

Hi.

What about the surrogate key transformation? That should solve it without messy workarounds.

https://learn.microsoft.com/en-us/azure/data-factory/data-flow-surrogate-key