0

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?

Improve this question

2 Answers 2

Reset to default
3

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: enter image description here

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

Check the output files and data in it: enter image description here

enter image description here

HTH.

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

Thank you for your detailed answer that is also a way to do it .
@Golan You're are welcome! I think using Data Flow is much easier, just choose the way you like best. If the answer is helpful for you, hope you can accept it as answer. This can be beneficial to other community members. Thank you and merry Christmas!
2

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) enter image description here

  1. The batch job can be any nbr of parallel executions enter image description here

enter image description here

  1. Create a parameterised dataset: enter image description here

enter image description here

5) enter image description here

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

Input :

enter image description here

Output:

enter image description here

Improve this answer

6 Comments

didnt work... I got this error - Operation on target ForEach1 failed: Activity failed because an inner activity failed
@Golan : Please see the above edited answer. That should help you in the set up of the pipeline. Note : Country is equivalent to Entity in example
Nandan , Thank you so much for your detailed answer - it's flawless and perfect , thank you !
@Golan Glad I could be of help :) hope you can accept it as answer as this can be beneficial to other community members as well.
Hello @Nandan . is it possible to add a filter to the source query in the copy data activity . for example - Copy activity i) Source : Dynamic Query Select * from t1 where State=@item().State AND I want to add to the query a filter by city = 'London'
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.