Azure Data factory, How to incrementally copy blob data to sql

Question

I have a azure blob container where some json files with data gets put every 6 hours and I want to use Azure Data Factory to copy it to an Azure SQL DB. The file pattern for the files are like this: "customer_year_month_day_hour_min_sec.json.data.json"

The blob container also has other json data files as well so I have filter for the files in the dataset.

First question is how can I set the file path on the blob dataset to only look for the json files that I want? I tried with the wildcard *.data.json but that doesn't work. The only filename wildcard I have gotten to work is *.json

Second question is how can I copy data only from the new files (with the specific file pattern) that lands in the blob storage to Azure SQL? I have no control of the process that puts the data in the blob container and cannot move the files to another location which makes it harder.

Please help.

Answer 1

You could use ADF event trigger to achieve this.

Define your event trigger as 'blob created' and specify the blobPathBeginsWith and blobPathEndsWith property based on your filename pattern.

For the first question, when an event trigger fires for a specific blob, the event captures the folder path and file name of the blob into the properties @triggerBody().folderPath and @triggerBody().fileName. You need to map the properties to pipeline parameters and pass @pipeline.parameters.parameterName expression to your fileName in copy activity.

This also answers the second question, each time the trigger is fired, you'll get the fileName of the newest created files in @triggerBody().folderPath and @triggerBody().fileName. Thanks.