Synapse: Pass lookup value (column name) into a stored proc parameter

Question

I have below code where I get full set of file names and for all the file names I need to set the in_progress_flag =1 . Which is done by calling a stored proc.

The Output of Get Valid Entity is like below

{
"count": 2,
"value": [
    {
        "Entity_Name": "Member",
        "File_Name": "MemberPARQUET"
    },
    {
        "Entity_Name": "Dealer_Group",
        "File_Name": "Dealer_Group.PARQUET"
    }
]}

The Stored Procedure is having parameter, which will take file_name from the lookup to update its status to 1.

Currently I am using like below

@activity('Get Valid Entity Names').output.File_Name

Which is failing with error "The expression 'activity('Get Valid Entity Names').output.File_Name' cannot be evaluated because property 'File_Name' doesn't exist, available properties are 'count, value, effectiveIntegrationRuntime, billingReference, durationInQueue'.",

Any help on how to call a value from Lookup into a parameter please

Answer 1

The expression 'activity('Get Valid Entity Names').output.File_Name' cannot be evaluated because property 'File_Name' doesn't exist, available properties are 'count, value, effectiveIntegrationRuntime, billingReference, durationInQueue'."

The error you are getting because lookup output does not contain File_Name attribute directly its under the value array so to access it you need to use expression like below:

@activity('Get Valid Entity Names').output.value[0].File_Name

As value is an array above expression will fetch the filename from 0 the index element from value array.

The Stored Procedure is having parameter, which will take file_name from the lookup to update its status to 1.

If you want to iterate on each filename take Foreach activity and pass the expression for items as

@activity('Get Valid Entity Names').output.value

and then under Foreach take your store procedure activity and iterate on each file using @item().File_Name