Iterate through folder and subfolders to copy the contents of one specific file to sql using data factory pipeline

Question

Below are the details :

Container name : pradev

Files and folders are on Azure File storage.

Inside the container I have multiple folders and multiple subfolders

Ex : pradev < GS< 2020-Q4 < test.csv ,test1.csv, metainfo.csv
pradev < GS< 2021-Q1 < test.csv , test1.csv, metainfo.csv
pradev < ST< 2021-Q1 < test.csv , test1.csv, metainfo.csv
pradev < SE
pradev < XA < test.csv , test1.csv, metainfo.csv

What I want to do is, I want to look for just metainfo files available on all the folders or subfolders and copy the file into sql. Folders and subfolders should be dynamic, no hardcoded names.

Any help will be highly appreciated.

Thank you!

Answer 1

Hello anonymous user,

Thanks for the question and using MS Q&A platform.

As per my understanding you have source folders and sub folders in a fileshare in Azure File storage and you would like to copy a specific file data from all folders and sub folders to a destination SQL table. Please correct if my understanding is not accurate. In addition to @Nandan Hegde input, you can also follow below approach.

If my understanding is correct, then the simplest way to achieve your requirement is by using wild card file path option in your copy activity source settings and check the Recursively option to process all files in the input root folder and its subfolders recursively or just the ones in the selected folder. Don't input any file name in your data set filename settings as you will use wild card file path option at copy activity source settings.

Below is an example setting that would help you configure accordingly.

Hope this will help. Please let us know if any further queries.

------------------------------

• Please don't forget to click on or upvote button whenever the information provided helps you. Original posters help the community find answers faster by identifying the correct answer. Here is how
• Want a reminder to come back and check responses? Here is how to subscribe to a notification
• If you are interested in joining the VM program and help shape the future of Q&A: Here is how you can be part of Q&A Volunteer Moderators

Answer 2

Not sure what was the problem but my understanding was copied files from folders, when I used the solutions what happens it copies everything including folders, which is not what I want, I want only files to be copied out of subfolders, my problem is similar to this: folder1\subfolder1\subfolder2\subfolder3\subfolder4\file

All I want is to reach to the lowest subfolder and pick the file