none
Iterating through all the files in Azure Blob Storage

    Question

  • Hi,

    I am working with Azure Data Factory V2. I am having following problem:

    First Part: I have to access the files in Azure Blob Storage and execute one stored procedure activity for each of them.

    Second Part: The store procedure activity should take the name of each file and utilize in within the activity.

    Ragarding the second part, i am thinking of using stored procedure parameter for file name.However, i am not being able to figure out how to do the first part.

    Looking for suggestions/help.

    Thanks.

    Wednesday, November 7, 2018 3:28 PM

All replies

  • Hello,

    For the first part, could you use '@item' in a ForEach to loop to pass the filename to the stored procedure activity:

    https://docs.microsoft.com/en-us/azure/data-factory/control-flow-for-each-activity 

    Wednesday, November 7, 2018 9:29 PM
    Moderator
  • Hi,

    As Json_J mentioned before, the For each activity enables you to iterate on an array to perform the stored procedure on each item, but before that, you need to get the array of all the file names in the blob container, Get Metadata activity provides this capability. The below pipeline definition should meet your requirement:

    {
        "name": "pipelineTest",
        "properties": {
            "activities": [
                {
                    "name": "Get Metadata1",
                    "type": "GetMetadata",
                    "typeProperties": {
                        "dataset": {
                            "referenceName": "YourAzureBlobDataset",
                            "type": "DatasetReference"
                        },
                        "fieldList": [
                            "childItems"
                        ]
                    }
                },
                {
                    "name": "ForEach1",
                    "type": "ForEach",
                    "dependsOn": [
                        {
                            "activity": "Get Metadata1",
                            "dependencyConditions": [
                                "Succeeded"
                            ]
                        }
                    ],
                    "typeProperties": {
                        "items": {
                            "value": "@activity('Get Metadata1').output.childItems",
                            "type": "Expression"
                        },
                        "activities": [
                            {
                                "name": "Stored Procedure1",
                                "type": "SqlServerStoredProcedure",
                                "typeProperties": {
                                    "storedProcedureName": "[dbo].[StoredProcedureTestWithParameters]",
                                    "storedProcedureParameters": {
                                        "fileName": {
                                            "value": {
                                                "value": "@item().name",
                                                "type": "Expression"
                                            }
                                        }
                                    }
                                },
                                "linkedServiceName": {
                                    "referenceName": "AzureSql1",
                                    "type": "LinkedServiceReference"
                                }
                            }
                        ]
                    }
                }
            ]
        }
    }

    Thanks.

    Thursday, November 8, 2018 2:26 AM
  • Hi,

    Thank you so much for the detailed solution.

    I followed every steps in this solution but getting error, "Invalid value for property value". The first Metadata activity is resulting the desired output.The problem now is in the foreach activity. I have @fileName nvarchar(max) as stored procedure parameter.

    What could have gone wrong?

    Thanks.

    Thursday, November 8, 2018 4:15 PM
  • Would you please share one piece of sample data of the Get Metadata activity output? Was the error "Invalid value for property value" showing up during pipeline execution? On for each activity?

    Thanks.

    Friday, November 9, 2018 1:42 AM
  • Hi,

    The output of Get Metadata Activity looks like this:

    {
        "childItems": [
            {
                "name": "file1.json",
                "type": "File"
            },
            {
                "name": "file2.json",
                "type": "File"
            },
      {
                "name": "file5.txt",
                "type": "File"
            },
            {
                "name": "file3.json",
                "type": "File"
            },
            {
                "name": "file4.json",
                "type": "File"
            }
           
        ],
        "effectiveIntegrationRuntime": "IntegrationRuntimeName",
        "executionDuration": 15
    }

    I am getting this error during the execution of ForEach Activity.

    When I try to trigger the whole pipeline, it fails and there is blank error page when I click the View Run .

    Thanks


    • Edited by ChetanSK Friday, November 9, 2018 8:56 AM
    Friday, November 9, 2018 8:51 AM
  • Hi,

    I didn't find anything wrong in the output data of the get metadata activity, basically speaking, if you follow the json definition I posted above, "@item().name" would capture the filename and be passed to the parameter "fileName" of the stored procedure script. If the error still exists, please share with me the failed pipeline runID and activity runID by email: wangzhan@microsoft.com. Thanks for your patience.

    Sunday, November 11, 2018 8:47 AM