Azure Data Factory V2 update an SQL record

Question

I would like to update values in mysql database in Azure Data Factory Copy Activity. Basically, I would like to update columns corresponding to file copy logging something similar to the example below

UPDATE database.filetable
set database.filetable.file_date = '@{utcNow()}'
WHERE database.filetable.file_id = 'xyz'

Is it possible? and what would be the correct way to do it. When this logic I get an error:

Failure happened on 'Source' side. ErrorCode=InvalidParameter,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=The value of the property 'schema' is invalid: 'Value cannot be null. Parameter name: schema'.,Source=,''Type=System.ArgumentNullException,Message=Value cannot be null. Parameter name: schema,Source=Microsoft.DataTransfer.ClientLibrary,'

Why not use simply the mysql function NOW() or UTC_TIMESTAMP() — nbk
Hi @elasticSol, hope you're doing well. If my answer is helpful for you, hope you can accept it as answer. This can be beneficial to other community members. Thank you. — Leon Yue
Hi @LeonYue I like your work-around but I'm having challenges — elasticSol
@elasticSol what's the challengens? We all glad to help you. — Leon Yue
Hi @elasticSol, no matter which challenges you have, you can post the question here. Please tell us. — Leon Yue

Leon Yue Leon Yue · Accepted Answer · 2021-04-05T02:48:19

Data Factory parameter doesn't support expression/function. I would suggest you use the variable and set variable active to achieve that.

For example:

Then pass the variable to your copy active, query option dynamic content like this:

@concat('UPDATE database.filetable set database.filetable.file_date =',variables('current_date'),'  
WHERE database.filetable.file_id = xyz')

HTH.

Azure Data Factory V2 update an SQL record

1 Answers