none
Azure Data Factory - item() is not a recognized function

    Question

  • Hi Team

    I am trying to create a pipeline.I have followed the below steps.

    Step 1: I have created a lookup activity which has the input as store procedure from Azure SQL database. The stored procedure will return three columns.I have given the three columns in the schema of azure SQL database

    Step 2:I have created a For each activity and connected it to the lookup activity .In the items tab i have given the code @activity('LookupConfigFile').output.value

    Step 3:I am creating a copy activity inside foreach where the filename and directory is based on the columns from the stored procedure.In the file path i am trying to access the lookup activity column using @item().SourceFile

    In step 3,I am getting a warning as 'item' is not a recognized function.

    Please help!!.

    Thanks

    Shankar

    Thursday, August 16, 2018 7:22 AM

Answers

All replies

  • Hi,

    @item() is recognized in activity level.

    You can define a parameter in dataset, and set the parameter's value as @item().SourceFile

    Thursday, August 16, 2018 8:24 AM
  • Thanks a lot for your quick resolution!!.

    I have done the necessary changes with the dataset and parameters,But i am still getting an issue.

    { "errorCode": "400", "message": "Activity failed because an inner activity failed", "failureType": "UserError", "target": "ForEachSourceFile" }

    Please find the below JSON created for pipeline activity

    "name": "pipeline1",
    "properties": {
    "activities": [
    {
    "name": "LookupConfigFile",
    "type": "Lookup",
    "policy": {
    "timeout": "7.00:00:00",
    "retry": 0,
    "retryIntervalInSeconds": 30,
    "secureOutput": false
    },
    "typeProperties": {
    "source": {
    "type": "SqlSource",
    "sqlReaderStoredProcedureName": "[dbo].[uspGetConfigValues]"
    },
    "dataset": {
    "referenceName": "AzureSqlTable_Configurations",
    "type": "DatasetReference"
    },
    "firstRowOnly": true
    }
    },
    {
    "name": "ForEachSourceFile",
    "type": "ForEach",
    "dependsOn": [
    {
    "activity": "LookupConfigFile",
    "dependencyConditions": [
    "Succeeded"
    ]
    }
    ],
    "typeProperties": {
    "items": {
    "value": "@activity('LookupConfigFile').output.value",
    "type": "Expression"
    },
    "isSequential": true,
    "activities": [
    {
    "name": "CopySourceFile",
    "type": "Copy",
    "policy": {
    "timeout": "7.00:00:00",
    "retry": 0,
    "retryIntervalInSeconds": 30,
    "secureOutput": false
    },
    "typeProperties": {
    "source": {
    "type": "FileSystemSource",
    "recursive": true
    },
    "sink": {
    "type": "AzureDataLakeStoreSink"
    },
    "enableStaging": false,
    "dataIntegrationUnits": 0
    },
    "inputs": [
    {
    "referenceName": "SourceDataset_ghl",
    "type": "DatasetReference",
    "parameters": {
    "SourceFile2": {
    "value": "@item().SourceFile",
    "type": "Expression"
    }
    }
    }
    ],
    "outputs": [
    {
    "referenceName": "DestinationDataset_ghl",
    "type": "DatasetReference"
    }
    ]
    }
    ]
    }
    }
    ]
    },
    "type": "Microsoft.DataFactory/factories/pipelines"
    }

    Thanks

    Shankar


    Thursday, August 16, 2018 10:10 AM
  • It shows the inner activity failed.

    Could you provide the error message from inner copy activity?

    Thanks.

    Friday, August 17, 2018 1:48 AM
  • Thanks for your suggestion.

    The copy activity is inside the foreach activity.I am not sure on how to debug the inner copy activity.

    Please find the  JSON for copy activity

    {
    "name": "CopySourceFile",
    "type": "Copy",
    "policy": {
    "timeout": "7.00:00:00",
    "retry": 0,
    "retryIntervalInSeconds": 30,
    "secureOutput": false
    },
    "typeProperties": {
    "source": {
    "type": "FileSystemSource",
    "recursive": true
    },
    "sink": {
    "type": "AzureDataLakeStoreSink"
    },
    "enableStaging": false,
    "dataIntegrationUnits": 0
    },
    "inputs": [
    {
    "referenceName": "SourceDataset_ghl",
    "type": "DatasetReference",
    "parameters": {
    "SourceFile2": {
    "value": "@item().SourceFile",
    "type": "Expression"
    }
    }
    }
    ],
    "outputs": [
    {
    "referenceName": "DestinationDataset_ghl",
    "type": "DatasetReference"
    }
    ]
    }

    In the sourcedataset_gh1 i have created a parameter SourceFile2

    and have assigned the value of item().SourceFile in the dataset properties of copy activity

    Please help.


    shankar


    Friday, August 17, 2018 2:48 AM
  • Thanks a lot for your help Bo.

    I have got the issue,the pipeline is working fine now.


    shankar

    Friday, August 17, 2018 3:33 AM
  • Hi Shankar, 

    How was the issue fixed, i am having the same issue in copying the file from one blob to another.


    "errorCode": "400", "message": "Activity failed because an inner activity failed", "failureType": "UserError", "target": "MyForEachActivity" }
    • Edited by Avi_2412 Thursday, September 27, 2018 8:11 AM
    Thursday, September 27, 2018 8:10 AM