ADF Creating a LookUp Activity Query with a Variable

Question

We have a bug in Final Testing. I am trying to make the following Query in a LookUp Activity:

SELECT ShortName, EEOXrefCode FROM [MercerStagingDev].[MILKY-WAYTEST\AppSQLVST4DotNetDev-R].[Copy of Battelle Work Assignment Fields Jobs] WHERE XRefCode = @variables('JobXRefCode')

I have the Set Variable showing the correct String 'JobXrefCode' but am getting a crazy error. Please help; I must figure out how to do this "syntax" this weekend. Thanks!! Mike Kiser

Error code 2100 Troubleshooting guide Failure type User configuration issue Details Failure happened on 'Source' side. ErrorCode=SqlOperationFailed,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=A database operation failed with the following error: 'Must declare the scalar variable "@variables".',Source=,''Type=System.Data.SqlClient.SqlException,Message=Must declare the scalar variable "@variables".,Source=.Net SqlClient Data Provider,SqlErrorNumber=137,Class=15,ErrorCode=-2146232060,State=2,Errors=[{Class=15,Number=137,State=2,Message=Must declare the scalar variable "@variables".,},],'

Answer 1

Please try this expression:

SELECT ShortName, EEOXrefCode 
FROM [MercerStagingDev].[MILKY-WAYTEST\AppSQLVST4DotNetDev-R].[Copy of Battelle Work Assignment Fields Jobs] 
WHERE XRefCode = '@{variables('JobXRefCode')}'

---

Update:

Expressions can also appear inside strings, using a feature called string interpolation where expressions are wrapped in @{ ... }. For example: "name" : "First Name: @{pipeline().parameters.firstName} Last Name: @{pipeline().parameters.lastName}"

Using string interpolation, the result is always a string.

So when you do something like this SQL: SELECT * FROM TableName WHERE ColumnName = 'value', you can use this expression.

Here are some samples in ADF Tutorials:

Ref:

Expressions and functions in Azure Data Factory

Incrementally load data from Azure SQL Database to Azure Blob storage using the Azure portal

Incrementally load data from multiple tables in SQL Server to a database in Azure SQL Database using the Azure portal