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.
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, thanks for your reply.
I'm implementing this solution:
- Lookup activity to read the SQL view in warehouse, getting some rows with some columns (source prefix file and month end date);
- Get metadata activity to access to on-premise folder with csv files and to get the child items;
- For each activity to iterate respect to lookup output --> @activity('Lookup').output.value;
inside the For each setting two variables to save source prefix file and month end date for the current item; - Filter activity to filter the output child items returned from the Get metadata activity.
The filter condition used is:
And(
startswith(item().name, variables('SourcePrefixFile')),
contains(item().name, variables('MonthEndDate'))
)I think that the For each should be sequential.
Thanks