adf "Property selection is not supported on values of type 'String'"

Question

I am trying to pick only selected files for copying using filter activity. The previous activity is an azure function which lists out the file names and the parallelly i am using lookup to restrict file names that are present in the database table only. But when I run the filter activity i am getting the following error.

Operation on target Filter1 failed: The execution of template action 'FilterFilter1' failed: The evaluation of 'query' action 'where' expression '@hamed1374 (contains(join(activity('Lookup1').output.value,','),item().file_list))
' failed: 'The expression 'not(contains(join(activity('Lookup1').output.value,','),item().file_list)) ' cannot be evaluated because property 'file_list' cannot be selected. Property selection is not supported on values of type 'String'.

Below is the output from azure function:
{
"file_list": [
"{\"source_container_name\": \"\", \"source_path\": \"\", \"source_file_location\": \"//MediFiles_20221109.zip\", \"source_file_name\": \"MediFiles_20221109.zip\", \"source_delimiter\": \"\", \"source_escape_character\": \"\", \"source_quote_character\": \"\", \"source_file_format\": \"zip\", \"target_location\": \"compute/xxpath//\", \"target_file_format\": \"\", \"target_container_name\": \"compute\", \"target_path\": \"xxpath/\", \"target_file_name\": \"MediFiles_20221109.zip\", \"target_delimiter\": \"\", \"target_escape_character\": \"\", \"target_quote_character\": \"\", \"sheet_name\": \"\", \"header_flag\": \"true\", \"host\": \"sftp.com\", \"port\": \"22\", \"user\": \"1409005\", \"pwd_key\": \"med-sftp-password\", \"prev_date\": \"Null\", \"curr_date\": \"Null\"}",

"{\"source_container_name\": \"\", \"source_path\": \"\", \"source_file_location\": \"//MediFiles_20221207.zip\", \"source_file_name\": \"MediFiles_20221207.zip\", \"source_delimiter\": \"\", \"source_escape_character\": \"\", \"source_quote_character\": \"\", \"source_file_format\": \"zip\", \"target_location\": \"compute/xxpath//\", \"target_file_format\": \"\", \"target_container_name\": \"compute\", \"target_path\": \"xxpath/\", \"target_file_name\": \"MediFiles_20221207.zip\", \"target_delimiter\": \"\", \"target_escape_character\": \"\", \"target_quote_character\": \"\", \"sheet_name\": \"\", \"header_flag\": \"true\", \"host\": \"sftp.com\", \"port\": \"22\", \"user\": \"1409005\", \"pwd_key\": \"med-sftp-password\", \"prev_date\": \"Null\", \"curr_date\": \"Null\"}"
}
]
Lookup input:
select * from wallfiles where dataset_id not in (10004,10005,10006)

Error:

The execution of template action 'FilterFilter1' failed: The evaluation of 'query' action 'where' expression '@hamed1374 (contains(activity('Get_Files_SFTP').output,item().Lookup1))' failed: 'The expression 'not(contains(activity('Get_Files_SFTP').output,item().Lookup1))' cannot be evaluated because property 'Lookup1' cannot be selected. Property selection is not supported on values of type 'String'.

Answer 1

Hi @Vinodh247 ,

Thankyou for using Microsoft Q&A platform and thanks for posting your question here.

As I understand your ask, you are trying to compare the output json of Azure function vs look up activity output and fetch out only those records which are matching and filter out the records which are not matchig. Please let me know if my understanding is incorrect.

Since there are two sources and each of the json out of Az function output needs to be compared with each record of lookup activity, you need to use two loops here (ForEach activity) in order to iterate through each of the JSONs and perform the comparison.

Kindly follow these steps:

1. Use Foreach after Az function and iterate through the output array using expression: @activity('Get_Files_SFTP').output.value[0].file_list
2. Inside foreach, use execute pipeline activity to call child pipeline. Create parameter inside the child pipeline say , 'FileName' and pass the value as 'item().source_file_name' through the execute pipeline activity.
3. In the child pipeline, use look up activity select * from wallfiles where dataset_id not in (10004,10005,10006). Assuming the filename is present in the column called col1 in wallfiles.
4. Use Foreach activity to iterate through the output of lookup activity using expression: activity('Lookup1').output.value
5. Use filter inside foreach activity, and use the expression: @not(contains(pipeline().parameters.FileName,item().col1))

Hope this will help. Please let us know if any further queries.

------------------------------

• Please don't forget to click on or upvote button whenever the information provided helps you.
  Original posters help the community find answers faster by identifying the correct answer. Here is how
• Want a reminder to come back and check responses? Here is how to subscribe to a notification
• If you are interested in joining the VM program and help shape the future of Q&A: Here is how you can be part of Q&A Volunteer Moderators