Check If the Array contains value in Azure Data Factory

user_2037281 0
Viewed 5388

I need to process files in the container using Azure Datafactory and keep a track of processed files in the next execution.

so I am keeping a table in DB which stores the processed file information, In ADF I am getting the FileNames of the processed files and I want to check whether the current file has been processed or not.

I am Using Lookup activity: Get All Files Processed to get the processed files from DB by using below query:

select FileName from meta.Processed_Files;

Then I am traversing over the directory, and getting File Details for current File in the directory by using Get Metadata Activity: "Get Detail of Current File in Iteration"

and in the If Condition activity, I am using following Expression:

@not(contains(activity('Get All Files Processed').output.value,activity('Get Detail of current file in iteration').output.itemName))

This is always returning True even if the file has been processed

How do we compare the FileName from the returned value

Output of activity('Get All Files Processed').output.value

{
"count": 37,
"value": [
    {
        "FileName": "20210804074153AlteryxRunStats.xlsx"
    },
    {
        "FileName": "20210805074129AlteryxRunStats.xlsx"
    },
    {
        "FileName": "20210806074152AlteryxRunStats.xlsx"
    },
    {
        "FileName": "20210809074143AlteryxRunStats.xlsx"
    },
    {
        "FileName": "20210809074316AlteryxRunStats.xlsx"
    },
    {
        "FileName": "20210810074135AlteryxRunStats.xlsx"
    },
    {
        "FileName": "20210811074306AlteryxRunStats.xlsx"
    },

Output of activity('Get Detail of current file in iteration').output.itemName

"20210804074153AlteryxRunStats.xlsx"
azure-data-factory-pipeline azure-data-factory-2 azure-data-factory
2 Answers

I often pass this type of thing off to SQL in Azure Data Factory (ADF) too, especially if I've got one in the architecture. However bearing in mind that any hand-offs in ADF take time, it is possible to check if an item exists in an array using contains, eg a set of files returned from a Lookup.

Background

Ordinary arrays normally look like this: [1,2,3] or ["a","b","c"], but if you think about values that get returned in ADF, eg from Lookups, they they look more like this:

{
    "count": 3,
    "value": [
        {
            "Filename": "file1.txt"
        },
        {
            "Filename": "file2.txt"
        },
        {
            "Filename": "file3.txt"
        }
    ],
    "effectiveIntegrationRuntime": "AutoResolveIntegrationRuntime (North Europe)",
    "billingReference": {
        "activityType": "PipelineActivity",
        "billableDuration": [
            {
...

So what you've got is a complex piece of JSON representing an object (the return value of the Lookup activity plus some additional useful info about the execution), and the array we are interested in is within the value object. However it has additional curly brackets, ie it is itself an object.

Solution

So the thing to do is to pass to contains something that will look like your object which has the single attribute Filename. Use concat to create the string and json to make it authentic:

@contains(activity('Lookup').output.value, json(concat('{"Filename":"',pipeline().parameters.pFileToCheck,'"}')))

Here I'm using a parameter which holds the filename to check but this could also be a variable or output from another Lookup activity.

Sample output from Lookup:

Lookup output

The Set Variable expression using contains:

contains expression

The result assigned to a variable of boolean type:

Result

user_15275040

I tried something like this.

  1. from SQL table, brought all the processed files as comma-separated values using select STRING_AGG(processedfile, ',') as files in lookup activity

  2. Assign the comma separated value to an array variable (test) using split function @split(activity('Lookup1').output.value[0]['files'],',')

  3. meta data activity to get current files in directory

  4. filter activity to filter the files in current directory against the processed files

items:

@activity('Get Metadata1').output.childitems

condition:

@not(contains(variables('test'),item().name))
user_149973040
Related