How to pass entire SQL statement to Lookup activity inside ForEach of Azure Data Factory?

Question

1. I have Lookup, which get list of SQL queries from database.

   SELECT source_query FROM [sales].[ListOfSQLQueries]
   

2. I have ForEach which Item. It included list of Select statements.

   @activity('Fetch Source Query').output.value 
   

3. I have inside ForEach Loopkup. It should make query to database based on source_query.

   @{item().source_query}
   

Input value of Lookup inside Query is:

   sqlReaderQuery": "\r\n'SELECT * FROM CUSTOMERS'\n"

Error is:

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

Does it look like there is extra strings in connection of SQL query?

How to pass entire SQL statement to Lookup activity inside ForEach of Azure Data Factory?

Pipeline:

{
    "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": []
    }
}

Answer 1

You don't need to add single quote ' in query of the lookup activity inside foreach.

Your pipeline should look like this:

{
    "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": []
    }
}

Here is my successful run using your SELECT 1 test case: