Share via

I need to append a rownumber to each line and copy file to adls storage account. Is it possible to do in copy activity ?

Varun Mourya Karanam 51 Reputation points
2023-01-12T12:46:25.65+00:00

I have a psv file in blob storage. I need to append a Row Number to each line and copy file to ADLS storage account. Is it possible to do in copy activity ?

01 - has 214 columns, 02 - has 18 cols 07 - has 4 cols. So the appending of Row Number needs to be dynamic.

Input :

User's image

Desired output :

Append Row Number to each row at end of line.

Tried to do using additional column in copy activity > rowNumber() but the outpu was like below. It appended the word "rowNumber" at last of each line.

User's image

Can anyone help ?

Azure Data Factory
Azure Data Factory
An Azure service for ingesting, preparing, and transforming data at scale.
11,652 questions
0 comments
Accepted answer
  1. MarkKromer-MSFT 5,226 Reputation points Microsoft Employee Moderator
    2023-01-13T00:01:29.76+00:00

    Use a Data Flow activity with the Surrogate Key transformation. That will add a row number to your dataset.

    0 comments

2 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Nandan Hegde 36,151 Reputation points MVP Volunteer Moderator
    2023-01-12T12:55:44.12+00:00

    Based on my understanding, unfortunately it is not directly possible via Copy activity.

    You might need to use Data flow activity by creating a derived column for row number and adding it in sync file.

    1 person found this answer helpful.
  2. KranthiPakala-MSFT 46,642 Reputation points Microsoft Employee Moderator
    2023-01-18T22:18:05.4933333+00:00

    Hi VC,

    The answer is no, there is no easy way to do it in Copy activity and copy activity is intended to do data movement between data stores. For this requirement, as @Mark Kromer MSFT called out in order to add an additional column for row serial number, you will have to use Mapping dataflow and use Surrogate Key Transformation. Below is sample:

    User's image

    User's image

    Here is how the settings looks like for Surrogate Key.
    User's image

    Here is the documentation for Surrogate Key transformation: Surrogate key transformation in mapping data flow

    Hope this info 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.

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.