Synapse Analytics Pipeline Expression (Azure Data Factory Expression)

Question

I need to develop a Synapse Analytics pipeline to load csv files from blob storage, I need some assistance with the required condition expression based on this requirement for an if-activity that loops through every csv files in a blob storage and loads them to 2 separate tables based on this condition "files prefixed with a name like 'gross_new_charges_202_--.csv' and csv files >= 'gross_new_charges_2022-11-05.csv' should be loaded to Staging02 table' else other csv files loaded to 'staging01 table" The reason the files are loaded to a different table is because of the field length in the files from a specific period.

I have tried this expression; "@and(contains(item().name, '^net_new_commission_202_\d{2}-\d{2}-\d{2}.csv$'), greaterOrEquals(concat(substring(item().name, 25, 4), substring(item().name, 29, 2), substring(item().name, 32, 2)), '20221105'))" but it doesn't work.

Answer 1

You can use an expression like below in the if activity.

@and(startswith(item().name, 'gross_new_charges_202'),greaterOrEquals(int(concat(substring(item().name, 18, 4), substring(item().name, 23, 2), substring(item().name, 26, 2))),20221105))

Here, I have used the above expression for the input files like below.

gross_new_charges_2022-10-05.csv
gross_new_charges_2022-11-05.csv
gross_new_charges_2022-12-05.csv
sample_blank.csv
Table2.csv

You need to change the index and matching string as per your file names.

Inside if True activities, I have stored @item().name in a Set variable activity for testing. Add the activities inside the True and False activities as per your requirement.

Result: