1

Product: Azure Data Factory

Component: Copy data Activity

Issue Description: I am encountering an error when using Azure Data Factory's Copy data Activity to execute a SQL query. The issue arises when querying a table whose name contains spaces, special characters, and the SQL keyword 'AS'.

SQL Query: The SQL query I am trying to execute is: SELECT [timestamp],[Name],[VAT Registration NO_],[Name 2] FROM dbo.[AS Processing Data$Data Information] WHERE [timestamp] > 0

Error Message: The error message I received from ADF is:

ErrorCode=SqlOperationFailed,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=A database operation failed with the following error: 'Incorrect syntax near the keyword 'AS'.',Source=,''Type=System.Data.SqlClient.SqlException,Message=Incorrect syntax near the keyword 'AS'.,Source=.Net SqlClient Data Provider,SqlErrorNumber=156,Class=15,ErrorCode=-2146232060,State=1,Errors=[{Class=15,Number=156,State=1,Message=Incorrect syntax near the keyword 'AS'.,},],

Suspected Cause: It appears that ADF is misinterpreting the SQL keyword 'AS' in the table name, treating it as a keyword instead of part of the table name, despite it being enclosed in brackets. This might be due to an issue with how ADF parses and interprets SQL queries.

Impact: This issue is preventing me from executing a valid SQL query in ADF and copying data from my source table.

Does anyone know about a workaround this? Seems to be a bug within Azure Data Factory?

I've tried most things I can think of. The query I input in the Copy task activity runs fine in SQL Management Studio. So it must be the way ADF is interpreting the brackets, for me it seems like ADF removes the brackets, so it's sending this request to the SQL DB:

SELECT [timestamp],[Name],[VAT Registration NO_],[Name 2] FROM dbo.AS Processing Data$Data Information WHERE [timestamp] > 0

If I write this query in SSMS I get the same error with 'AS'

Daniel A
  • 11
  • 1
  • 2
    I’d make your life much easier in all sorts of areas by not creating tables whose name contains spaces, special characters, and the SQL keyword 'AS'. – NickW Jun 05 '23 at 14:49
  • 1
    I have not encountered this exact problem, but here are a couple potential workarounds:. 1) create a view to perform the SELECT and rename the columns in SQL. 2) Try using Data Flow instead of Copy, it uses a different parser and engine. I would be curious to see if it could handle the syntax. – Joel Cochran Jun 05 '23 at 15:55
  • The error was related to the pre-copy script in the Sink tab in the copy task actually, after some hours of debugging... The issue was that the pre-copy script didn't have brackets around the table-name. Thanks for the replies. – Daniel A Jun 06 '23 at 13:43

1 Answers1

0

I used [] around the schema and table name and it seems to be working.

  • copy activity:

cp ref

  • preview

preview ref

  • ssms ssms ref
Ashwin Mohan
  • 108
  • 9