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
Hi pmscorca
Yes, ForEach is the right approach here. You will use ForEach to loop through each SQL row.Thank You.
