How to Pass Variable Value to Script Task to Update Table

Question

I have a dynamic variable. How do you pass it to a script task in an ADF pipeline to update a table? I tried this. The error is "Incorrect synax near 'MydB'."

update MyDB.dbo.MyTable

set column1 = @variables('MyDynamicVariable')),

Answer 1

Hey,

Can you please suggest which database are you executing the script ? (Azure SQL DB, SQL MI etc)?

Also is that the complete logic within the script task?

Below should be the sample aspect :
update MyDB.dbo.MyTable

set column1 = @{variables('MyDynamicVariable')}

And can you provide a screenshot of the input window of script task after execution :

And copy the text and try executing the script as is on database via SSMS as a debugging purpose as well

Answer 2

Hi Lilly W

You can use the below format. (with a where clause, too)

@concat('update MyDB.dbo.MyTable set column1 =',variables('MyDynamicVariable'),
' where someothercolumn= ',variables('someothervariable'))

As Nandan mentioned, you can use the query without concat too by mentioning variable name inside { }, like below -

update MyDB.dbo.MyTable set column1 = @{variables('MyDynamicVariable')}

Please try and let us know if this helps.