Azure 数据工厂事件副本从 foreach 事件获取多个表名称

Question

使用数据工厂 V2，我尝试实现从一个 Azure SQL 数据库到另一个数据库的数据流复制。我必须将多个表合并到一个目标表中。

为此，我创建了一个查找事件，该事件创建要复制的三个表的名称。输出 JSON 文件被传递到 foreach 事件，该事件应将每个表的数据复制到目标表中，但管道执行不成功。

我在下面报告了我的管道的代码:

{
"name": "FLD_Item_base",
"properties": {
    "activities": [
        {
            "name": "myLookup",
            "type": "Lookup",
            "dependsOn": [
                {
                    "activity": "myStoredProcedure",
                    "dependencyConditions": [
                        "Succeeded"
                    ]
                }
            ],
            "policy": {
                "timeout": "7.00:00:00",
                "retry": 0,
                "retryIntervalInSeconds": 30,
                "secureOutput": false,
                "secureInput": false
            },
            "typeProperties": {
                "source": {
                    "type": "SqlSource",
                    "sqlReaderQuery": "SELECT tsy_company_desc, tsy_tabella_desc, tsy_company_id, \ncase when tsy_company_desc = 'all' \nthen '['+ tsy_tabella_desc + ']' else '['+tsy_company_desc + '$' + tsy_tabella_desc + ']'  end as nome_tabella_completo \nfrom dbo.TSY_FLUSSI_LOAD_DWH \nwhere tsy_flusso_desc = 'item_base' \nand tsy_tabella_desc='Item' \n"
                },
                "dataset": {
                    "referenceName": "TLD_Item",
                    "type": "DatasetReference"
                },
                "firstRowOnly": false
            }
        },
        {
            "name": "myStoredProcedure",
            "type": "SqlServerStoredProcedure",
            "policy": {
                "timeout": "7.00:00:00",
                "retry": 0,
                "retryIntervalInSeconds": 30,
                "secureOutput": false,
                "secureInput": false
            },
            "typeProperties": {
                "storedProcedureName": "[dbo].[myStoredProcedure]"
            },
            "linkedServiceName": {
                "referenceName": "Sink",
                "type": "LinkedServiceReference"
            }
        },
        {
            "name": "IterateSQLTables",
            "type": "ForEach",
            "dependsOn": [
                {
                    "activity": "myLookup",
                    "dependencyConditions": [
                        "Succeeded"
                    ]
                }
            ],
            "typeProperties": {
                "items": {
                    "value": "@activity('myLookup').output",
                    "type": "Expression"
                },
                "activities": [
                    {
                        "name": "FullCopyActivity",
                        "type": "Copy",
                        "policy": {
                            "timeout": "7.00:00:00",
                            "retry": 0,
                            "retryIntervalInSeconds": 30,
                            "secureOutput": false,
                            "secureInput": false
                        },
                        "typeProperties": {
                            "source": {
                                "type": "SqlSource",
                                "sqlReaderQuery": {
                                    "value": "SELECT * FROM @{item().value.name_tab}",
                                    "type": "Expression"
                                }
                            },
                            "sink": {
                                "type": "SqlSink",
                                "writeBatchSize": 10000
                            },
                            "enableStaging": false,
                            "dataIntegrationUnits": 0
                        },
                        "inputs": [
                            {
                                "referenceName": "Source",
                                "type": "DatasetReference"
                            }
                        ],
                        "outputs": [
                            {
                                "referenceName": "TLD_Item",
                                "type": "DatasetReference"
                            }
                        ]
                    }
                ]
            }
        }

管道执行返回以下错误:

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

有谁知道如何解决这个问题吗？谢谢

Answer 1

请尝试在 items 中使用 @activity('myLookup').output.value 而不是 @activity('myLookup').output > 事件 IterateSQLTables。

您可以找到查找事件的文档 here