Search code examples
azure-data-factorylookupazure-synapse

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

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 procedure.

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 has a 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

enter image description here

Solution

  • 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 stored procedure activity and iterate on each file using @item().File_Name