none
ADF V2 - For Each is not working. Error is no descriptive

    Question

  • If someone can see an obvious issue, can you please point it out?  It would be greatly appreciated.

    No error shows up in the Activity Log

    In the pipeline, the error is:  "Activity failed because an inner activity failed"

    Below is the code

    {
        "name": "For Each Source Table",
        "properties": {
            "activities": [
                {
                    "name": "ForEach",
                    "type": "ForEach",
                    "typeProperties": {
                        "items": {
                            "value": "@pipeline().parameters.tableList",
                            "type": "Expression"
                        },
                        "isSequential": false,
                        "activities": [
                            {
                                "name": "Copy from stored proc to table",
                                "type": "Copy",
                                "policy": {
                                    "timeout": "7.00:00:00",
                                    "retry": 0,
                                    "retryIntervalInSeconds": 30,
                                    "secureOutput": false
                                },
                                "typeProperties": {
                                    "source": {
                                        "type": "SqlSource",
                                        "sqlReaderStoredProcedureName": "concat(\"etl.sp\",@{item().TableName})"
                                    },
                                    "sink": {
                                        "type": "SqlSink",
                                        "writeBatchSize": 10000
                                    },
                                    "enableStaging": false,
                                    "cloudDataMovementUnits": 0
                                },
                                "inputs": [
                                    {
                                        "referenceName": "DS_ASD_Source_rt_Query",
                                        "type": "DatasetReference"
                                    }
                                ],
                                "outputs": [
                                    {
                                        "referenceName": "DS_ASD_Sink_dw_DynamicTable",
                                        "type": "DatasetReference",
                                        "parameters": {
                                            "SinkTableName": {
                                                "value": "concat(\"migratedstage.\",@(item().TableName})",
                                                "type": "Expression"
                                            }
                                        }
                                    }
                                ]
                            }
                        ]
                    }
                }
            ],
            "parameters": {
                "tableList": {
                    "type": "Object",
                    "defaultValue": {
                        "tableList": [
                            {
                                "TableName": "Table1"
                            }
                        ]
                    }
                }
            }
        }
    }





    • Edited by zl34 Friday, August 3, 2018 1:27 PM
    Monday, June 4, 2018 6:00 PM

Answers

  • Hi,

    Sorry for delayed response. Do you mean you're trying to pass different sqlReaderStoredProcedureName and SinktableName to different activities? If it's your scenario, define these two parameters in pipeline, and reference parameters in your copy activity. You may need foreach activity to iteratively pass value to the parameter in your several copy activities.

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

    Thanks.

    • Marked as answer by zl34 Tuesday, June 19, 2018 9:37 PM
    Saturday, June 9, 2018 3:46 AM

All replies

  • Hi, seems you want set sqlReaderStoredProcedureName and SinktableName as parameter, can you modify the "source" field and "SinkTableName" as the following and have a try?

    "source": {
     "type": "SqlSource",
     "sqlReaderStoredProcedureName": {
      "value": "@concat('etl.sp',item().TableName)",
      "type": "Expression"
     }
    }

    "SinkTableName": {

        "value": "@concat('migratedstage.',item().TableName)",
        "type": "Expression"
    }




    Tuesday, June 5, 2018 2:48 AM
  • Thank you for the suggestion.  I am still failing.  I am having the hardest time knowing how to pass these parameters from and to the different activities. I haven't found any helpful documentation yet.

    • Edited by zl34 Wednesday, June 6, 2018 6:38 PM
    Wednesday, June 6, 2018 6:15 PM
  • Hi,

    Sorry for delayed response. Do you mean you're trying to pass different sqlReaderStoredProcedureName and SinktableName to different activities? If it's your scenario, define these two parameters in pipeline, and reference parameters in your copy activity. You may need foreach activity to iteratively pass value to the parameter in your several copy activities.

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

    Thanks.

    • Marked as answer by zl34 Tuesday, June 19, 2018 9:37 PM
    Saturday, June 9, 2018 3:46 AM