Azure Data Factory CopyData Activity issue, error code 2200

Question

I am about to schedule the database insertion using Azure Data factory.

I am having 1 copydata and 2 lookups 1. Max Id inserted in destination table(LastValue in json code) 2. Max id from source table (Max).

To get the lookup value in Copy Data, i've written a query like i mentioned below but still i am getting error and i am unable to fetch Lookup value in select query.

select * from customer where created_at>=curdate()-30 and id > @{activity('CDNOps_LastUpdateValue').output.firstRow.LastValue} and id <= @{activity('Source_MaxValue').output.firstRow.Max limit 20000}

Please help me out on this.

The error message i am getting while scheduling the data is,

"errorCode": "2200", "message": "Failure happened on 'Source' side. ErrorCode=UserErrorOdbcOperationFailed,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=ERROR [42000] [Microsoft][MariaDB] You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '"LastValue":540418183}\n and id<={"Max":546239715}' at line 2.........

Wang Zhang Wang Zhang · Accepted Answer · 2018-12-05T01:47:27

the below syntax should be the right one:

select * from customer where created_at>=curdate()-30 and id > @{activity('CDNOps_LastUpdateValue').output.firstRow.LastValue} and id <= @{activity('Source_MaxValue').output.firstRow.Max} limit 20000

Please note that when you wrap lookup activity output into @{}, this means you're trying to dynamically evaluate the value at the run time, so put "limit 20000", which doesn't belong to the lookup output, into @{} cause the error. Also ensure that @{activity('CDNOps_LastUpdateValue').output.firstRow.LastValue} and @{activity('Source_MaxValue').output.firstRow.Max} would be evaluated into a value that is comparable with column id in the table (same type at least). A small tips for debugging, you could create a user property in copy activity on UI to actively check whether the evaluated query result is what you want, see the picture below:

Azure Data Factory CopyData Activity issue, error code 2200

1 Answers