Not able to use append variable in Use query of source (copy data) through For each loop

Question

Hi,

I am beginner and learning Data factory.

I am doing below requirement.

---a Table is having data like Bird name and Bird Type ( Mourning Dove , Dove).

--- Want to generate flat files as per Bird types dynamically.

1) used lookup on table to fetch distinct Bird Type and keeping in array variable

2) For each loop will read the array values in loop and this array value has to be passed to generate through query in "copy data" and with same above table as source.

3) Can some one suggest how can i use value getting into append variable during iteration in SQL query for generating flat file.

Not able to use append variable in Use query of source (copy data) through For each loop

Answer 1

Hi @abhilash yadav ,

Thank you for asking this question. In my opinion, the solution you want to achieve in the pipeline using CopyActivity would be too much expensive as too many Activities (i.e., CopyActivities and other Activities) will run just for a single pipeline run. You can achieve the same output in a way more simpler and cleaner way by using DataFlow.

The steps you have to do are:

1. create a DataFlow. To make folder path dynamic, you can add a parameter folderPath
2. add required sources (in the source activity, you can pull all data from SQL table or you can use Query, it's up to you)
3. do data manipulations (you can do a lot of things e.g., filter, aggregate, pivot, deriveColumns etc.)
4. before Sink, derive column "filePath" e.g., out/Birds/birdtype1.parquet
5. in the Sink --> Settings: select FilenameOption to "As data in column" and select column with filename to "filePath".
6. in Sink-->Optimize: Use Current Partition

That's all. All the data with the same filePath, will be written in that specific file. You will get all the files in the folderPath you mentioned in the pipeline.

Please see screenshots attached and let me know if you have any questions. If the above response helped, please "accept as answer" and "up-vote" the same! Thanks!