Question 103122 in Azure Question posted in
The official documentation to get you started can be found here.

How to modify source column in Copy Activity of Azure Data Factory

I am trying to copy data from a csv file into a database table. I created a pipeline in Azure Data Factory and added a Copy Activity. I set the source and the destination datasets.

During the copy operation, I want to prefix the values of the column Col1 in the source csv file with a fixed string. At the bottom of the Mapping section in the UI, there is an option such as "Add dynamic content". I guess I would be able to add an expression here for such a mapping but I could not find any information on how to do that.

What is the correct way of accomplishing this?

Tags:

2

Answers


  1. 0

    Adding a dynamic column value in copy activity supports only the ADF parameter /function values which would be common for all rows.

    As of now there is no direct support to modify a column at row by row level in copy activity.

    enter image description here

    You can do it via 2 ways:

    1. use copy activity to copy into staging table and then use an SP activity to modify from staging table to final tables
    2. use dataflow derived column transformation

    https://learn.microsoft.com/en-us/azure/data-factory/data-flow-derived-column

    Login or Signup to reply.
  2. 0
    • As directed by @Zorkolot and @Nandan, copy data activity in general is to deal with moving files/folders from different sources to different sinks.
    • Since the requirement is to add a fixed string prefix to an existing column, you need to use dataflows. Dataflows can help with manipulating/converting data as required.
    • You can use derived column transformation as shown in the below. I have added pre string as a prefix to id column.

    enter image description here

    • Another way is to use one copy data activity and a script activity to copy to the database and write an update query with concat function on the required column with prefix with a query like this:
    update t1 set <your_col>=concat('pre',<your_col>)

    enter image description here

    • Another way would be to use Python notebook to add the prefix to required column and then move it to database.
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search