Copy activity failed in for each activity with error 2200 source failed

Question

I been using lookup activity and for each activity to copy data from azure synapse(table) to adls (parquet). I have a total of 12 tables to go through for each loop. When I debug it 2 tables copy activities are shown as failed and the rest of them succeeded. the error I am getting is

*Failure happened on 'Source' side. ErrorCode=SqlOperationFailed,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=A database operation failed with the following error: 'Parse error at line: 1, column: 87: Incorrect syntax near 'Order'.',Source=,''Type=System.Data.SqlClient.SqlException,Message=Parse error at line: 1, column: 87: Incorrect syntax near 'Order'.,Source=.Net SqlClient Data Provider,SqlErrorNumber=103010,Class=16,ErrorCode=-2146232060,State=1,Errors=[{Class=16,Number=103010,State=1,Message=Parse error at line: 1, column: 87: Incorrect syntax near 'Order'.,},],'*  

same error for another table with the same column name.

When I remove these two columns from their tables and run they are successful.Both columns are name 'Order'(float) What is that I am getting wrong here

Answer 1

Hello @Guduru, Harihara Prasad and welcome to Microsoft Q&A.

This error message is saying there is a mistake in the query. You mentioned this happens with tables which contain the same column name. Is the column name "Order" by any chance?

"order" is a reserved keywork in SQL, as in Select name, id from mytable order by name desc;

You mentioned using a lookup. Might this be to fetch column names to add into the query? like Select @{item().columnName} from @{item().tableName}; ?
If so, I can imagine the result looking like Select Order from mytable; This would cause the above error.

to use reserved keywords as names, enclose them in [brackets] like Select [Order] from [mytable];

This can be accomplished like @concat( 'Select [' , item().ColumnName , '] from [' , item().tableName , '];' )