本文介绍了如何将整个SQL语句传递给Azure数据工厂ForEach内的Lookup活动?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

  1. 我有Lookup,它从数据库获取SQL查询列表。

    SELECT source_query FROM [sales].[ListOfSQLQueries]
    
  2. 每一项我都有。它包括选择语句列表。

    @activity('Fetch Source Query').output.value 
    
  3. 我在ForEach Loopkup内部。应根据SOURCE_QUERY对数据库进行查询。

    @{item().source_query}
    

查询中Lookup的输入值为:

   sqlReaderQuery": "
'SELECT * FROM CUSTOMERS'
"

错误为:

 [{Class=15,Number=102,State=1,Message=Incorrect syntax near 'SELECT '.,},],'

SQL查询连接中是否看起来有多余的字符串?

如何将整个SQL语句传递给Azure数据工厂ForEach内的Lookup活动?

管道:

{
    "name": "Data_quality_monitor",
    "properties": {
        "activities": [
            {
                "name": "Fetch SQL Query",
                "type": "Lookup",
                "dependsOn": [],
                "policy": {
                    "timeout": "7.00:00:00",
                    "retry": 0,
                    "retryIntervalInSeconds": 30,
                    "secureOutput": false,
                    "secureInput": false
                },
                "userProperties": [],
                "typeProperties": {
                    "source": {
                        "type": "AzureSqlSource",
                        "sqlReaderQuery": {
                            "value": "SELECT source_query FROM [Sales].[Customers]",
                            "type": "Expression"
                        },
                        "queryTimeout": "02:00:00",
                        "partitionOption": "None"
                    },
                    "dataset": {
                        "referenceName": "Customer_SQL_DB",
                        "type": "DatasetReference",
                        "parameters": {
                            "Schema": "Sales",
                            "Table": "Customers"
                        }
                    },
                    "firstRowOnly": false
                }
            },
            {
                "name": "ForEach Customers",
                "type": "ForEach",
                "dependsOn": [
                    {
                        "activity": "Fetch SQL Query",
                        "dependencyConditions": [
                            "Succeeded"
                        ]
                    }
                ],
                "userProperties": [],
                "typeProperties": {
                    "items": {
                        "value": "@activity('Fetch SQL Query').output.value",
                        "type": "Expression"
                    },
                    "isSequential": true,
                    "activities": [
                        {
                            "name": "Fetch Customers",
                            "type": "Lookup",
                            "dependsOn": [],
                            "policy": {
                                "timeout": "7.00:00:00",
                                "retry": 0,
                                "retryIntervalInSeconds": 30,
                                "secureOutput": false,
                                "secureInput": false
                            },
                            "userProperties": [],
                            "typeProperties": {
                                "source": {
                                    "type": "AzureSqlSource",
                                    "sqlReaderQuery": {
                                        "value": "'@{item().source_query}'",
                                        "type": "Expression"
                                    },
                                    "queryTimeout": "02:00:00",
                                    "partitionOption": "None"
                                },
                                "dataset": {
                                    "referenceName": "Customer_SQL_DB",
                                    "type": "DatasetReference",
                                    "parameters": {
                                        "Schema": "Sales",
                                        "Table": "Customers"
                                    }
                                },
                                "firstRowOnly": false
                            }
                        }
                    ]
                }
            }
        ],
        "variables": {
            "source_query": {
                "type": "String"
            },
            "destination_query": {
                "type": "String"
            }
        },
        "folder": {
            "name": "Customers"
        },
        "annotations": []
    }
}

推荐答案

您不需要在lookup活动的lookupforeach中添加单引号'

您的管道应该如下所示:

{
    "name": "Data_quality_monitor",
    "properties": {
        "activities": [
            {
                "name": "Fetch SQL Query",
                "type": "Lookup",
                "dependsOn": [],
                "policy": {
                    "timeout": "7.00:00:00",
                    "retry": 0,
                    "retryIntervalInSeconds": 30,
                    "secureOutput": false,
                    "secureInput": false
                },
                "userProperties": [],
                "typeProperties": {
                    "source": {
                        "type": "AzureSqlSource",
                        "sqlReaderQuery": {
                            "value": "SELECT source_query FROM [Sales].[Customers]",
                            "type": "Expression"
                        },
                        "queryTimeout": "02:00:00",
                        "partitionOption": "None"
                    },
                    "dataset": {
                        "referenceName": "Customer_SQL_DB",
                        "type": "DatasetReference",
                        "parameters": {
                            "Schema": "Sales",
                            "Table": "Customers"
                        }
                    },
                    "firstRowOnly": false
                }
            },
            {
                "name": "ForEach Customers",
                "type": "ForEach",
                "dependsOn": [
                    {
                        "activity": "Fetch SQL Query",
                        "dependencyConditions": [
                            "Succeeded"
                        ]
                    }
                ],
                "userProperties": [],
                "typeProperties": {
                    "items": {
                        "value": "@activity('Fetch SQL Query').output.value",
                        "type": "Expression"
                    },
                    "isSequential": true,
                    "activities": [
                        {
                            "name": "Fetch Customers",
                            "type": "Lookup",
                            "dependsOn": [],
                            "policy": {
                                "timeout": "7.00:00:00",
                                "retry": 0,
                                "retryIntervalInSeconds": 30,
                                "secureOutput": false,
                                "secureInput": false
                            },
                            "userProperties": [],
                            "typeProperties": {
                                "source": {
                                    "type": "AzureSqlSource",
                                    "sqlReaderQuery": {
                                        "value": "'@{item().source_query}'",
                                        "type": "Expression"
                                    },
                                    "queryTimeout": "02:00:00",
                                    "partitionOption": "None"
                                },
                                "dataset": {
                                    "referenceName": "Customer_SQL_DB",
                                    "type": "DatasetReference",
                                    "parameters": {
                                        "Schema": "Sales",
                                        "Table": "Customers"
                                    }
                                },
                                "firstRowOnly": false
                            }
                        }
                    ]
                }
            }
        ],
        "variables": {
            "source_query": {
                "type": "String"
            },
            "destination_query": {
                "type": "String"
            }
        },
        "folder": {
            "name": "Customers"
        },
        "annotations": []
    }
}
以下是我使用SELECT 1测试用例成功运行的示例:

这篇关于如何将整个SQL语句传递给Azure数据工厂ForEach内的Lookup活动?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!

10-23 14:56