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
}]

Kranthi Pakala · Accepted Answer · 2021-06-03 06:50:10Z

3

Unfortunately Copy activity is not suited for this requirement. You will have to use Mapping data flow in ADF.

Here are the sequence of transformations that are required to achieve your scenario:

First Source transformation
Derived Column transformation
Window Transformation
Sink Transformation

This has been answered in Microsoft Q&A forum with step by step implementation using Mapping dataflow : https://learn.microsoft.com/answers/questions/419209/index.html

answered Jun 3, 2021 at 6:50

Kranthi Pakala

1,4627 silver badges12 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Mutaz-MSFT · Accepted Answer · 2021-06-02 12:17:23Z

1

you probably need to use mapping data flow in ADF, and use the rowNumber() expression to achieve this.

answered Jun 2, 2021 at 12:17

Mutaz-MSFT

8066 silver badges20 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related