How to handle computed column in ADF while copying multiple tables?

Question

Dear Sirs,

I am trying to copy multiple tables from one SQL server DB to another on Azure using ADF. One of the tables is having compute column. So when it comes to compute column ADF gives error - cannot be modified because it is either a computed column or is the result of a UNION operator.
What can be done ? As it is a generic mapping I cannot manually modify or take it out also.
Is there any solution to it ?

Answer 1

Hello @Anonymous ,

Thanks for the question and using MS Q&A platform.

Is any of your sink table column is a Computed column and are you trying to insert/copy data into that column from your source? If that is the case, then you will run into above error message. A computed column is a virtual column that is not physically stored in the table but is computed from an expression that uses other columns in the same table. A computed column cannot be the target of an INSERT or UPDATE statement.

If you have a computed columns in your sink. then you cannot use auto-mapping to copy the data as it will also include mapping to your computed columns, and which will result in above issue.

Workaround:
If you know which tables have computed columns, then you can separate those tables from the automated mapping flow or you can create a new pipeline only for those tables with computed columns and have explicit mapping by removing the computed columns from the mapping. If you have more than one table, then you will have to define a dynamic mapping for each table and reference it during runtime based on the table names.

For more info about dynamic mapping in ADF please refer to this demo: Dynamic Column mapping in Copy Activity in Azure Data Factory

Here is the official doc related to this: Parameterize mapping

Hope this info helps.