File path in ADF Data Flow

Question

File path in ADF Data Flow

Ram 66

I'm getting files monthly twice into blob storage. The name of the file is abc_month name. Each time the month name will change. In data set instead of changing file path each time. Is there any alternative for it and I'm using data flow activity to run the pipeline.

1 answer

Your answer

Answer 1

AnnuKumari-MSFT 34,556 Microsoft Employee Moderator

Hi @Ram ,

As I understand your query , you want to fetch data from file which is loaded twice in a month in the blob storage, every month the file name changes so you are unsure how to pass the filename in the dataset dynamically. Please let me know if my understanding is incorrect.

You can make use of 'Get Metadata' activity and point the dataset within it to the folder containing the file. In the Field list , use ChildItems which will retrieve all the fileNames present within the folder.
Then , use filter activity with the expression @contains(substring(item().name,2,2),substring(startOfMonth(utcNow()),5,2)) , kindly modify the index position according to our fileName.

After filtering the fileNames , you can pass these filenames into dataflow to perform transformation.

Here is a short video for your reference:

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

Ram 66 Reputation points

2022-07-12T03:27:04.207+00:00

Hi @AnnuKumari-MSFT , Thanks for your response. As I said earlier I will get files to folders inside bolb storage twice in a month. The file name for the first time assume as (abc_jan). Each time the file name (abc) is constant and the month name will change.
So my file path in dataset looks like('container_name'/'folder_name'/'abc_jan'). So each time instead of change the file name inside data set is there any alternative.
In data flow activity Inside source there is source options. Inside source options I can see wildcard paths. Can i achive my scenario using wildcard option. Like the files name starting with (abc) it should be updated in data set folder path When a new file is arrived to blob container.
And each time l'm loading the file to stage I'm deleting the old file inside blob storage, so only updated file will exist inside blob container.
Thanks
AnnuKumari-MSFT 34,556 Reputation points Microsoft Employee Moderator

2022-07-12T10:32:28.593+00:00
Hi @Ram ,
You can use following approach to achieve the above requirement:

Create a variable FileName

Use Get metadata activity to fetch all the fileNames from the output folder.

Use LookUp activity and point the dataset to one of the SQL Server and provide this query SELECT DATENAME(MONTH,GETUTCDATE()) AS 'CurrentMonthName'

Use ForEach activity to iterate over GetMetadata activity output by using this as the item: @activity('Get Metadata1').output.childItems

Inside ForEach activity, use If block with this condition @contains(toLower(item().name),toLower(activity('Lookup1').output.value[0].CurrentMonthName))

Inside the True condition in IF block , use the fileName to set the file name in the variable by providing @item().name as the value . Use this fileName to perform further actions.

Please let me know if it helps you, if you specifically want to use dataflow to achieve the requirement , I can try to repro the same. Please do let me know .

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

If the above answer helped, Please do consider clicking Accept Answer and Up-Vote for the same as accepted answers help community as well. If you have any further query do let us know.
AnnuKumari-MSFT 34,556 Reputation points Microsoft Employee Moderator

2022-07-18T06:36:57.21+00:00

Hi @Ram ,
Just checking in to see if the above answer helped. Please do consider clicking Accept Answer and Up-Vote for the same as accepted answers help community as well. If you have any further query do let us know.

Share via

File path in ADF Data Flow

1 answer

Your answer