Data Upsert Process in Azure SQL

Question

I'm working on transferring a whole table from one database to several others, all in Azure SQL. To do this, I'm using a Lookup activity to get the list of server and database names. Then, I'm running a loop (ForEach) where I have a Copy Data activity. In this activity, I'm using a fixed dataset as the source and a changing destination.

My question is: When the loop runs, will it keep hitting the source database each time? I only want to pull the data once because it's the same data going to all the destination databases. Is there a way to pull the data once and keep it in memory instead of going back to the source each time the loop runs? Or, if I'm using a static dataset as the source, does that mean the data is pulled only once per pipeline run?

I hope this all makes sense!

Answer 1

Hi @Glasier

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

Based on your query, it seems like you are trying to transfer a whole table from one database to several others in Azure SQL. You are using a Lookup activity to get the list of server and database names, and then running a loop (ForEach) where you have a Copy Data activity. In this activity, you are using a fixed dataset as the source and a changing destination.

To answer your question in Azure Data Factory, using a static dataset as the Source in the Copy Data activity means that the data will be pulled only once per pipeline run, not every time the loop runs. This behavior is by design, and it ensures that the data is read from the source once and then processed for each iteration of the ForEach loop without hitting the source DB multiple times.

So, you can rest assured that the data will be fetched from the source database only once at the beginning of the pipeline run and will be stored in memory for use in all the iterations of the ForEach loop, where it will be upserted to the multiple destination databases.

Hope this helps. Do let us know if you any further queries.

---

If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.