Issue in incrementing ID dynamically in azure data factory without using data flows

Question

I am working with a table which is in Azure SQL Database, The ID is not in sequential order. I want to copy this data into data lake. Before copying the data, I want to dynamically increment the id either in the same column or add an additional column with auto increment in the same table. The requirement is not to use any data flows.. this have to be done only by using pipeline activities.

My Execution: I have tried using until loop by adding two parameters start=0, end=5 and two variables counter and temporary. Firstly, I have set a variable for initializing counter as start parameter and I have sent the counter into the until loop. Inside the loop, I have 2 set variable activities. In first set variable activity, I am trying to increment the counter by 1 and saving it in temporary variable. In the second set variable, I am copying the temporary variable value to the counter. After this, I am passing the counter into copy activity, and putting it in an additional column , which I am adding in Source tab. The increment is happening column level. I am trying to make this row level but failing.. Do you have any approach?

I am so sorry for making this complicated. Please find the screenshots in https://github.com/sarvani1929/azure/blob/main/issue.docx

Answer 1

You could use the ROW_NUMBER Window Function in a SQL Query to generate the row number as a new column:

Then use the Query in the Copy Activity Source.