azure – ADF pipeline, source is AZUE SQL and destination CSV in Blob Storage

Correct, the main concern is to have all the CDC and Incremental data in a single file

The copy activity will overwrite the target file every time with new data. So, using copy activity, you might not get your requirement done.

You can use Dataflow instead of copy activity. First, use copy activity before the for-loop. Copy only the first table (first row from lookup) from source table to target file. This will create the target table. After this copy activity, use for-each.

In the for-each, change the expression like taking from the second item to last in the lookup output array. Use skip() to skip the first item from the lookup output array.

Inside For-Each, take dataflow activity. Create a dataflow with 2 sources. Source1 is your target table dataset and Source2 is your source table dataset.

Next, add a Union transformation to both these two sources and do the union By name.

In the sink, add your target file dataset and go to sink settings, set the File name option to Output to single file. Give your target file name in the Output to single file like below.

You can provide values for the source dataset parameters in the Dataflow activity. Here, I have given a sample value. In your case give your value as per the for-loop.

The first copy activity will create your target file. Then, in each iteration of for-loop the dataflow will union each previous iteration file data with current source table data and copies it to the same file. In this way, after the end of for-loop, your target file will contain all the data from your source tables.