Share via

How to filter and copy files in Azure Data Factory without using loop over pipeline parameters?

Mohammad Saber 591 Reputation points
2023-12-10T03:29:47.8966667+00:00

Hi,

I want to copy files in a Container in an Azure blob storage which contains around 10,000,000 files.

Filename format looks like "Energy_ReportName_Timestamp_VersionNumber.zip". The sample filename could be "Energy_Payment_20231209110007_0000000404988124.zip". VersionNumber at the end of the filename doesn't have a regular pattern.

All files are located in the source container and there is no sub-folder or nested folders.

I want to filter zip files for a specific ReportName from StartDate until EndDate, and copy those files to the destination container. The filters are applied to the filename. Also, the filename must start with "Energy".

For example, files for "ReportName" = Payment; and StartDate = 20231101 (at any time on this date and any VersionNumber) and EndDate = 20231205.

Sample acceptable filenames are:

Energy_Payment_20231101110007_0000000404988124.zip

Energy_Payment_20231102110007_0000000404988124.zip

Energy_Payment_20231103110007_0000000404988124.zip

  • Energy_Payment_20231203110007_0000000404988124.zip

Since there are millions of files in the source container, I am looking for an approach that is fast to find the desired files and copy them to the sink container using the Copy activity in Azur eData Factory.

I want to avoid using a loop over every date since it makes it slower to filter files for one day (due to having a large number of files in the container), copy files, and then repeat the process for the next days.

Also, I tried the "Get Metadata" activity to get a list of files but it failed due to the large number of files in the container with below message:

The length of execution output is over limit (around 4MB currently)

Is there any way that I can filter or select all desired files, and copy them in one run?

Azure Data Factory
Azure Data Factory
An Azure service for ingesting, preparing, and transforming data at scale.
11,655 questions
0 comments

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Subashri Vasudevan 11,231 Reputation points
    2023-12-10T08:33:45.4166667+00:00

    Hi,

    Looks like there is another thread that you have created on similar lines as mentioned below.

    You would get required support in that thread itself.

    https://learn.microsoft.com/en-us/answers/questions/1459427/what-is-the-efficient-way-to-filter-and-copy-files

    Requesting you to close this thread by accepting this as answer.

    Thanks

    1 person found this answer helpful.
  2. KranthiPakala-MSFT 46,642 Reputation points Microsoft Employee Moderator
    2024-01-09T23:17:37.67+00:00

    @Mohammad Saber Thanks for posting the query.

    In case if your file modified date is similar to that of the date filter you are looking to filter the files, then you can use below settings to filter the files as per your requirement.

    User's image

    Use filter by last modified feature of copy activity to filter only the files that fits your modified date requirement.

    Hope this info helps.

    Please don’t forget to Accept Answer and Yes for "was this answer helpful" wherever the information provided helps you, this can be beneficial to other community members.

    0 comments

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.