如何将整个SQL语句传递给Azure数据工厂ForEach内的Lookup活动？ [英] How to pass entire SQL statement to Lookup activity inside ForEach of Azure Data Factory?
本文介绍了如何将整个SQL语句传递给Azure数据工厂ForEach内的Lookup活动？的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我有Lookup，它从数据库获取SQL查询列表。
SELECT source_query FROM [sales].[ListOfSQLQueries]每一项我都有。它包括选择语句列表。
@activity('Fetch Source Query').output.value我在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活动？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
