Unclosed quotation mark after the character string ERROR on Copy Activity ADF

Question

I have a Copy Activity and I am using a SQL Query in the source of the copy activity to fetch some data from SQL Server, The SQL query has a where condition to it for which the value is passed as a pipeline parameter.

However when I run the copy activity it fails with the below error.

ErrorCode=SqlOperationFailed,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=A database operation failed with the following error: 'Incorrect syntax near '
WHERE PlanName= '.
Unclosed quotation mark after the character string '
'.',Source=,''Type=System.Data.SqlClient.SqlException,Message=Incorrect syntax near '
WHERE PlanName= '.
Unclosed quotation mark after the character string '
'.,Source=.Net SqlClient Data Provider,SqlErrorNumber=102,Class=15,ErrorCode=-2146232060,State=1,Errors=[{Class=15,Number=102,State=1,Message=Incorrect syntax near '
WHERE PlanName= '.,},{Class=15,Number=105,State=1,Message=Unclosed quotation mark after the character string '
'.,},],'

SQL Query that I use in the source of my copy activity.

SELECT TOP 10 * FROM dbo.[Master] WITH (NOLOCK)

where PlanName='@{pipeline().parameters.PlanName}'

Copy Activity Input that gets generated.

{ "source": {

    "type": "SqlServerSource",

    "sqlReaderQuery": **"SELECT TOP 10 *\n  FROM dbo.[Master] WITH (NOLOCK)\nwhere PlanName='testplan'"**,

    "queryTimeout": "02:00:00",

    "partitionOption": "None"

},

Can anyone please help with this issue.

Answer 1

Hi

Thanks for providing the latest updates on the reported issue, here is the revised expression you can try -

@concat('SELECT TOP 10 * FROM dbo.[Master] WITH (NOLOCK) WHERE PlanName = ''', string(pipeline().parameters.PlanName), '''')

Some additional validation steps -

Make sure the PlanName parameter is being passed correctly into the pipeline. Sometimes, unexpected characters or extra quotes can sneak in and mess with the SQL query, so it’s worth double-checking the value.

Also, before running the query in ADF, try pasting the full SQL string into SQL Server Management Studio (SSMS) or any SQL client you use. It’s a quick way to see if the query itself is valid and helps you figure out whether the issue is with the query logic or how it’s being built in the pipeline.

Thanks,
Vrishabh

Answer 2

Hello Zaygum,

Thank you for your question and for providing the detailed error message and query. This is a very common issue in Azure Data Factory (ADF) when dynamically constructing SQL queries, and the "Unclosed quotation mark" error is a classic sign of a string concatenation problem.

You are constructing your SQL query by directly embedding the pipeline parameter into the query string like this: where PlanName='@{pipeline().parameters.PlanName}'.

While this looks correct, the ADF expression builder and the SQL engine can sometimes misinterpret the quotes and newlines, especially when building the final JSON payload for the activity. The error message Incorrect syntax near ' WHERE PlanName= ' strongly suggests that the parameter value and its surrounding quotes are not being rendered correctly into the final SQL command.

The Solution: Use String Concatenation

The most reliable way to fix this is to use ADF's built-in string concatenation function, concat(), to build your query. This method explicitly tells the expression engine how to assemble the string, which avoids any ambiguity with quotes and special characters.

Here is how you should modify your query in the Copy Activity's source settings:

1. Go to your Copy Activity in the ADF pipeline.

In the Source tab, instead of typing the query directly into the text box, click on "Add dynamic content".

In the expression builder window that pops up, enter the following expression:

text
@concat('SELECT TOP 10 * FROM dbo.[Master] WITH (NOLOCK) WHERE PlanName = ''', pipeline().parameters.PlanName, '''')

Why This Works

@concat(...): This function explicitly joins multiple string parts together into a single string.

'SELECT ... WHERE PlanName = ''': This is the first part of your SQL query. Notice the three single quotes at the end. The first and last quotes define the string literal in ADF's expression language. The middle quote is an escaped single quote that will be included in the final SQL command.

pipeline().parameters.PlanName: This injects the value of your PlanName parameter (e.g., 'testplan').

'''': This is the final part of the string. It's a single quote that closes the SQL string literal, which itself is enclosed in single quotes for the ADF expression.

When the expression is evaluated, it will correctly build a clean, valid SQL query string like: SELECT TOP 10 * FROM dbo.[Master] WITH (NOLOCK) WHERE PlanName = 'testplan'

This will be passed to the SQL Server, resolving the syntax error and the "unclosed quotation mark" issue. This approach is more robust and is the recommended best practice for building dynamic queries in ADF.

Best Regards,

Jerald Felix