How to append a string value in the column value in Copy Activity in ADF

Question

How to append a string value in the column value in Copy Activity in ADF

Ankit Kumar 91

In below copy activity screen shot , i am trying to create a new column(PartitionKey) whose value is same as DocType column. I would also like to insert one string value along with Doctype value. something like concat("recoding",$.Doctype) as value of PartitionKey. I am unable to do so and would like to know if this is possible to do.

I want this because I get error for some items where DocType key is not present. and I would like to avoid that.

The error message i get is below and I am not sure for which item this error occurs as there are thousands of items getting copied.

ErrorCode=DuplicateColumnNotFound,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=,Source=,''Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=Column name '$.DocType' to be duplicated is not found in the source data. Please check the source column name

0 comments

1 answer

Your answer

Answer 1

KranthiPakala-MSFT 46,822 Microsoft Employee Moderator

Hi @Ankit Kumar ,

Thanks for using this forum and posting your query.

The reason for this error is because you are using an incorrect syntax $.DocType, it should be just the column name as DocType . This would avoid the error and copy the values of DocType column to your newly added column PartitionKey.
For the additional requirement of concatenating a string value to this additional column - I don't think this is possible within copy activity and Copy activity is not intended for this purpose. If you would to do a data transformation then I would recommend you to please use Mapping Data flow in ADF which is better suited for transformations. Here is a list of supported transformations by Mapping dataflow : Mapping data flow transformation overview
You can use a Derived column transformation which would help to achieve your requirement.

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.

Ankit Kumar 91 Reputation points

2021-07-19T04:56:50.777+00:00

I used derived column in mapping data flow and it worked fine, but with mapping data flow the issue is I don't get all the values of my document in sink. its probably because data flow takes only the first 10 documents as sample and deduce the schema from there and if any new columns comes after these 10 documents then those are not taken.
KranthiPakala-MSFT 46,822 Reputation points Microsoft Employee Moderator

2021-07-22T08:31:26.787+00:00

Hi @Ankit Kumar ,

Sorry for the delayed response. It shouldn't be that behavior. If you are trying to preview the data then it is limited to a sample dataset/records as you mentioned, but when you Debug run/Trigger now the pipeline then all the data from source to be copied to sink. If this is not the behavior you have noticed, then I would recommend you to please open a new thread with as much as information possible (like sample source data without sensitive info, related screenshots of the issue and output file with the limited data you are seeing). This would help us understand the root cause and assist you better.

Please let us know how it goes.

----------

Please don’t forget to Accept Answer and Up-Vote wherever the information provided helps you, this can be beneficial to other community members.
Ankit Kumar 91 Reputation points

2021-07-22T09:32:22.54+00:00

You are wrong here. Please check Troubleshoot mapping data flow documentation from Microsoft
https://learn.microsoft.com/en-us/azure/data-factory/data-flow-troubleshoot-connector-format#azure-cosmos-db-and-json-format
KranthiPakala-MSFT 46,822 Reputation points Microsoft Employee Moderator

2021-07-23T18:19:02.447+00:00

Hi @Ankit Kumar ,

I apologies for misunderstanding the problem and thanks much for clarifying with additional details.
And yes, you are correct, this is by design that ADF uses sample rows (for example, top 100 or 1000 rows data) to infer the schema, and the inferred result will be used as a schema to read data. So if your data stores have extra columns that don't appear in sample rows, the data of these extra columns are not read, moved or transferred into sink data stores. ADF product team suggest to follow the recommendation provided in the troubleshooting guide document you have shared in the previous response.

And if you have any additional feedback for improvements of this behavior, I would encourage you to please share it in User voice forum : https://feedback.azure.com/forums/270578-data-factory. All the feedback shared in this forum are actively monitored and reviewed by ADF product team.

Do let us know if you have further query.

Thank you
KranthiPakala-MSFT 46,822 Reputation points Microsoft Employee Moderator

2021-07-26T22:35:36.927+00:00

Hi @Ankit Kumar ,

Following up to see if you still need assistance on this requirement. Please let us know so that we will continue to engage.

Thank you.

Share via

How to append a string value in the column value in Copy Activity in ADF

1 answer

Your answer