azure-data-factoryoracle-adf

ADF Compare list from sql query to getMetaData output

I have a SQL table that contains the columns FileNamePrefix and TableName. I have files in blob storage that begin with the FileNamePrefix with some datetime in the name:

SQL table:

FileNamePrefix   TableName
---------------  ---------- 
File1            Table1 
File2            Table2 
File3            Table3 etc..

Files in blob storage:

File1_20240101.xlsx 
File2_20240102.xlsx

I'm using a Lookup Activity to get the records from the SQL table. I'm trying to use a Get Metadata activity to see what files are in storage, then do a ForEach only on the files from the lookup that have a corresponding file in storage.

I've tried to use the Filter activity with various options, but nothing seems to do what I need.

In the data above, I want to only send the ForEach activity File1/Table1 and File2/Table2 as File3 has no file in storage.

Solution

  • then do a ForEach only on the files from the lookup that have a corresponding file in storage

    To get the Lookup files that have a corresponding file in storage, you should loop through the Get meta data child items. Inside, For-Each you can get the required lookup row using Filter activity.

    First give the Get meta data child items array to For-Each activity.

    enter image description here

    Filter activity and For-Each both uses expression @item() as current item, so store the current item file name of the For-Each in a variable prior to Filter activity.

    enter image description here

    In the Filter activity, give the Lookup output array @activity('Lookup1').output.value as items and in the condition give the below expression.

    @startswith(variables('curr_item_filename'), item().FileNamePrefix)

    enter image description here

    This filters out the required Table name for each file name in the storage in each iteration.

    enter image description here

    If you want to use the above table name, you can use the expression @activity('Filter1').output.value[0].TableName after Filter activity in each iteration.

    In case, if there is no Lookup Table name for the current file name from the storage, the Filter will give empty array. So, if you have such records, better to check the Filter activity output array length in an if activity and if its more than 0, then go ahead with your activities inside the True activities of if activity.