How to pick date from file name and copy to sql and attaching event trigger

Question

we have files as 'ROME 30092023' & 'ROME 07102023' in ADLS container, We need to copy both files, so How to fetch both files based on older date first and next latest date and having with event trigger

for the trigger the variable/parameter value should not be empty in default value

Answer 1

The Expression for extracting date: @substring(item().name, 5, 8) item() represents the current file being processed in the ForEach loop.

If you have an array of filenames, loop through them with the ForEach activity.

Inside the loop, use the extracted date to compare with a reference date stored in the varReferenceDateTime variable.

If the extracted date is more recent than the reference date, update the varReferenceDateTime variable and the fileNameToBeProcessed variable.

Here is demonstration by a community expert: Azure Data Factory - Copy the latest file with DATE PART to Folder using AZURE FUNCTIONS.

Use the "Get Metadata" activity in ADF to retrieve the list of filenames from the ADLS container.

{
    "name": "GetMetadataAndCopy",
    "properties": {
        "activities": [
            {
                "name": "GetMetadata",
                "type": "GetMetadata",
                "linkedServiceName": {
                    "referenceName": "Your_ADLS_LinkedService",
                    "type": "LinkedServiceReference"
                },
                "typeProperties": {
                    "dataset": {
                        "referenceName": "Your_ADLS_Dataset",
                        "type": "DatasetReference"
                    },
                    "fieldList": ["childItems"]
                }
            },
            {
                "name": "ForEach",
                "type": "ForEach",
                "typeProperties": {
                    "items": {
                        "value": "@activity('GetMetadata').output.childItems",
                        "type": "Expression"
                    },
                    "activities": [
                        {
                            "name": "CopyData",
                            "type": "CopyData",
                            "typeProperties": {
                                "source": {
                                    "type": "BlobSource"
                                },
                                "sink": {
                                    "type": "SqlSink"
                                },
                                "input": {
                                    "referenceName": "Your_Input_Dataset",
                                    "type": "DatasetReference"
                                },
                                "output": {
                                    "referenceName": "Your_Output_Dataset",
                                    "type": "DatasetReference"
                                }
                            }
                        }
                    ]
                }
            }
        ]
    }
}

Answer 2

Hi Madhu ,

It seems you want to copy files from an ADLS container sorting the filename from older to latest. You also want to attach the pipeline with an event trigger.

Kindly check out the approach followed in this video to sort the files based on the date in their filenames: Find latest file in ADLS based on date on filename

On top of that you can add Copy activity at the end and point the source dataset to the filename coming out of the last set variable activity.

Here is how you can use Event based Triggers in Azure Data Factory

Kindly let me know if you get stuck in the implementation . If the above suggestions helped, please do consider accepting the answer by clicking on Accept answer button. Thankyou