How to simplify the logic to grab multiple files with similar naming pattern?

Question

How to simplify the logic to grab multiple files with similar naming pattern?

King Java 790

I have following 9 csv files:

User's image

In Azure Data Factory, I have been using 9 individual pipelines with each one with a wildcard to grab files like this:

@concat('CA_Employee_List_1',formatDateTime(utcNow(),'MMddyyyy'),'*','.csv')

....

@concat('CA_Employee_List_9',formatDateTime(utcNow(),'MMddyyyy'),'*','.csv')

But, rather than having 9 separate pipelines, I would like to use ForEach or something dynamic way to grab all 9 files in a better method so I could possibly only use 1 pipeline.

What is an approach to go thru each file name using a wild card like indicated on the top?

Currently, I only have two activities (Copy data --> Stored Procedure):

User's image

Just FYI:

I believe if I use "Get Metadata" activity, I cannot use wildcard.

So, I have been using "Lookup" activity for looking at file name (for other pipeline).

User's image

Thanks.

Accepted answer

0 additional answers

Your answer

Answer 1

Nandan Hegde 36,156 MVP Volunteer Moderator

Hey,

you can use Range function :

[https://learn.microsoft.com/en-us/azure/data-factory/control-flow-expression-language-functions#range

to create an integer array from 1-9 and use a for each iteration with range array as input.

You can create a dynamic source dataset with parameterized dataset where @concat('CA_Employee_List_',item(),',formatDateTime(utcNow(),'MMddyyyy'),'*','.csv')

King Java 790 Reputation points

2023-01-21T00:08:17.9133333+00:00

@Nandan Hegde Thank you for your help.

So, I have changed the structure of pipeline where "Copy data" and "Stored Procedure" go inside ForEach.

I am not sure whether this is a right way of doing it.

I created a Variable like this:

And then, inside of ForEach, I have both "Copy data" and "Stored Procedure".

Inside the Wildcard path, this is how expression look like:

But, I am not sure why it would not even show the data when I clicked the "Preview" data.

When I run the pipeline, nothing gets inserted.

I am not sure how I fix this issue.

Thanks..
King Java 790 Reputation points

2023-01-24T00:48:00.5733333+00:00

@Nandan Hegde I would like to follow up. I have updated the content from previous comment. Thanks.
Nandan Hegde 36,156 Reputation points MVP Volunteer Moderator

2023-01-24T11:58:24.6433333+00:00

Hey, Sorry to have missed out on this thread.

You need to remove item() from quotes and test it out
King Java 790 Reputation points

2023-01-24T18:37:07.4+00:00

@Nandan Hegde Thank you so much for your feedback. That makes the pipeline works.

I am not sure it is appropriate to ask you one more question.

(I just created a separate question on the forum, so you could answer there or answer here if you want:

[https://learn.microsoft.com/en-us/answers/questions/1163886/how-to-loop-(foreach)-thru-copy-data-and-execute-s )

I am having an issue with how Loop (ForEach) should work to insert data once.

Currently, I have "Copy data" (which grabs file) and "Stored Procedure" (which inserts data to SQL) and located inside "ForEach" activity which creates duplicated inserts when I tested with two csv files.

So, I moved the "Stored Procedure" outside the "ForEach" which resulted inserting only one csv file's data (instead of two csv files' data).

How do we make the "ForEach" work to loop thru two csv files and insert once?

Thanks.
Nandan Hegde 36,156 Reputation points MVP Volunteer Moderator

2023-01-25T03:50:32.79+00:00

glad it helped out. I have replied back in the new mail thread
King Java 790 Reputation points

2023-01-25T16:51:22.23+00:00

@Nandan Hegde Thank you so much. I will respond to your feedback after I try.

Share via

How to simplify the logic to grab multiple files with similar naming pattern?

0 additional answers

Your answer