ADF Exclude Column from all tables in Copy Data task

Singh, Gurprateek 25 Reputation points
2023-10-19T17:30:17.5766667+00:00

Hi All,

I am using Copy task to copy around 200 tables from source and I need to exclude 1 column (with same name) from all 200 tables.

Is there any way to exclude column while copying using for each iteration in pipeline.

I tried with Dataflow as well, but was not able to parameterize expression in AlterRow Insert/Update.

Would be helpful if someone could provide solution with Copy Task or Dataflow.

Azure Data Factory
Azure Data Factory
An Azure service for ingesting, preparing, and transforming data at scale.
9,751 questions
{count} votes

1 answer

Sort by: Most helpful
  1. KranthiPakala-MSFT 46,427 Reputation points Microsoft Employee
    2023-10-19T22:16:28.1466667+00:00

    @Singh, Gurprateek Thanks for using Microsoft Q&A forum and posting your query.

    Using copy data activity could be challenging and it may need dynamic mapping implementation and the solution will be cumbersome.

    Since all the tables have same column name which you would like to exclude, then data flow makes more sense in this case.

    In mapping data flow after your source, have a select transformation and in that you can write below condition to exclude a particular column.

    In the Input columns section for Source column section have this condition name != "yourColumnNameThatHasToBeExcluded" and in the Name as section have $$ that means select transformation will consider or select all columns and values expect the only column that was mentioned in this condition. Below is an example:

    User's image

    User's image

    When you do a data preview of your select transformation, you will see all columns except the one mentioned in the condition.

    User's image

    Hope this helps.


    Please don’t forget to Accept Answer and Yes for "was this answer helpful" wherever the information provided helps you, this can be beneficial to other community members.

    0 comments No comments