Forum Discussion
Filtering files using a SQL view or table
you already have everything that you need in the lookup. The simplest approach would be:
Lookup gets rows: (prefix, month_end_yyyymmdd)
Get-metadata gets all filenames (childItems)
Filter activity keeps only files where:
filename startswith(prefix) AND filename contains(month_end)
In Fabric pipelines the filter condition looks like this:
And(
startswith(item().name, item().prefix),
contains(item().name, item().month_end_yyyymmdd)
)If your lookup returns an array of objects, wrap the filter under a ForEach on the lookup output, but do NOT do multiple Get Metadata calls:
1 Get Metadata
1 ForEach over lookup records
Inside: Filter on the same childItems list
This prevents repeated enumeration of the folder. You generate a single list of files and apply logical filtering per prefix/date combination.
Hi pmscorca ,
Here are a few additional best practices and enhancements you can consider to make your pipeline more reliable, maintainable, and scalable when filtering and copying historical end-of-month CSV files from on-prem folders using a SQL view + Data Pipeline approach in Microsoft Fabric:
Make Filtering Case-Insensitive (If Needed) -- If filenames might have inconsistent casing (e.g.,SourcePrefix_20240131.csv)
Trim Extra Whitespace in Lookup Output-- To ensure the prefix and month_end columns are trimmed to prevent false negatives
If this post helps, then please appreciate giving a Kudos or accepting as a Solution to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!
Hi pmscorca
Following up to confirm if the earlier responses addressed your query. If not, please share your questions and we’ll assist further.
Ok, about this issue I think that the For each should be sequential, isn't it?
Thanks
