question

NavinDondapati avatar image
0 Votes"
NavinDondapati asked HimanshuSinha-MSFT answered

Azure Data Factory use Set variaable in Lookup query

Hi Guys,

We have String variable Var_SetPipelineID and we have for each loop; Assigned inside we have set variable as below
Var_SetPipelineID =@item().Name


We have added on success Lookup Activity in Settings Query as below

SELECT [PipelineID]
FROM [dbo].[Pipeline]
Where [PipelineID]=@{variables('Var_SetPipelineID')}


got below error:


Failure happened on 'Source' side. ErrorCode=SqlOperationFailed,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=A database operation failed with the following error: 'Invalid column name '1'.',Source=,''Type=System.Data.SqlClient.SqlException,Message=Invalid column name '1'.,Source=.Net SqlClient Data Provider,SqlErrorNumber=207,Class=16,ErrorCode=-2146232060,State=1,Errors=[{Class=16,Number=207,State=1,Message=Invalid column name '1'.,},],'

1 is actual value in the variable.

azure-data-factory
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

1 Answer

HimanshuSinha-MSFT avatar image
0 Votes"
HimanshuSinha-MSFT answered

Hello @NavinDondapati ,
Thanks for the ask and using the Microsoft Q&A platform .

Please use a a dynamic expression like

@concate('SELECT [PipelineID] FROM [dbo].[Pipeline] Where [PipelineID]=','''',@{variables('Var_SetPipelineID')},''''')


and it should work . The idea is to construct the select statement dynamically .


Please do let me know how it goes .
Thanks
Himanshu
Please do consider clicking on "Accept Answer" and "Up-vote" on the post that helps you, as it can be beneficial to other community members

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.