How to export from sql server table to multiple csv files in Azure Data Factory

Question

I have a simple clients table in sql server that contains 2 columns - client name and city. In Azure Data Factory, how can I export this table to multiple csv files that each file will contain only a list of clients from the same city, which will be the name of the file

I already tried, and succeeded, to split it to different files using lookup and foreach, but the data remains unfiltered by the city

any ideas anyone?

Answer 1

You would need to follow the below flow chart:

1. LookUp Activity : Query : Select distinct city from table
2. For each activity
   Input : @activity('LookUp').output.value a) Copy activity i) Source : Dynamic Query Select * from t1 where city=@item().City

This should generate separate files for each country as needed

Steps: 1)

2. The batch job can be any nbr of parallel executions

4. Create a parameterised dataset:

5)

Result: I have 2 different Entities, so 2 files are generated.

Input :

Output:

Answer 2

You can use Data Flow to achieve that easily.

I made an example for you. I create a table as source, export this table to multiple csv files that each file will contain only a list of clients from the same city, which will be the name of the file.

Data Flow Source:

Data Flow Sink settings: File name options: as data in column and use auto mapping.

Check the output files and data in it:

HTH.