Azure Data Factory – ForEach overwriting records instead of inserting

Question

I have followed this guide to create a for each loop retrieving the data I need and it is working, however I would like all my JSON records in a single file, but the copy task is overwriting the previously imported record and I cannot see how to make this an insert/upsert rather than overwriting.
Due to my lack of experience and probably asking the wrong questions, Google has not been my friend and I have been unable to adapt this to meet my needs. I have tried changing copy behaviour from none to merge but this still did not solve the issue.
Can anyone advise how to achieve what I am after?

Answer 1

Hi @Mia Lancaster ,

Welcome to Microsoft Q&A platform and thanks for posting your question here.

As I understand your query, you are trying to copy json records from REST API using Copy activity in ADF pipeline. However, the issue you are facing is that the data is getting overwritten after every iteration of copy activity inside foreach. Please let me know if my understanding is incorrect.

Copy activity will overwrite the files if you provide the same file name in your sink. Copy activity doesn't support append data to existing file.

If you would like to write all data to a single file from multiple sources then you will have to copy the files for each iteration to an intermediate folder by appending datetime to the filename ( using parameterized dataset at the sink) and then once all the iterations are completed then have another subsequent copy activity outside of your ForEach activity and point it's source to the intermediate folder and in sink point to your desired file store and then use copyBehavior as MergeFiles in copy activity settings.

Check here for more details: Copy activity - File system as sink

Hope this will help. Please let us know if any further queries.

------------------------------

• Please don't forget to click on or upvote button whenever the information provided helps you.
  Original posters help the community find answers faster by identifying the correct answer. Here is how
• Want a reminder to come back and check responses? Here is how to subscribe to a notification
• If you are interested in joining the VM program and help shape the future of Q&A: Here is how you can be part of Q&A Volunteer Moderators