I need to upsert an entire table from a source DB to multiple destination DBs (all Azure SQL). I have a Lookup activity that pulls the list of server/DB names and passes it to a ForEach loop, inside which I've placed a Copy Data activity with a static dataset as the Source and a dynamic Destination.
My question is this: Will this arrangement hit the source DB every time the loop runs? I really only need to pull the data once since the exact same data needs to be upserted to all the destination DBs. Is there a way to pull the data one time and dump it to memory somehow instead of going back to the source DB every time the loop runs? Or by definition, does my use of a static dataset as the Source mean that the data is only pulled once per pipeline run?
Does any of this make any sense? :)
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.