How to pass a dynamic parameter as SQL query in for SQL dataset in Azure Data Flow?

Question

I am trying to run dynamic queries with a parameter in Azure Data Flow but I continue to get the "Column operands are not allowed in literal expressions" error. The parameter value is "SELECT * FROM Table WHERE Column in ('D1234','D1234','D1234','D8585','D6565','D7777','D4949','D2066','D2259','D5432')". I have tried all the ways to enter this parameter value, "{$parameter1}", {$parameter1}, $parameter1, ($parameter1) and I still continue to get the frustrating error. I have looked at all the documentation and forums relating to this issue but all of them continue to suggest the same ways I have tried to enter the value and none of it works. Any assistance would be greatly appreciated.

Answer 1

Hi @Dimeji Olayinka ,
Apologies for the delay in response.

Thanks for providing the detailed screenshots of the entire workflow. I tried to reproduce your scenario by creating a set variable activity which contains SQL query and trying to pass it to data flow as an expression in the parameter.

When I manually passed the SQL query as the parameter while checking the data preview in data flow, I was able to get the desired result. However, I faced the same issue when I executed the pipeline even after checking the expression checkbox while calling dataflow in the pipeline. I concluded that the output of set variable activity is not getting passed correctly, which led me to explicitly pass the double quotes by concatenating with the output of set variable activity.

In the parameter value of data flow activity, kindly use the following code to concat double quotes explicitly over the variable :

@concat('"',string(variables('S')),'"')  

Here is the video you can refer to:

For the Authorization error, Could you please check to ensure that you have the Storage Blob Data Contributor role assigned to you on the storage account? If the roles are set correctly, then it might seem to be an intermittent issue . However, I am checking on this with internal team . I will let you know once I hear back from them .

Hope this will help. Please let us know if any further queries.

------------------------------

• Please don't forget to click on or upvote button whenever the information provided helps you.
  Original posters help the community find answers faster by identifying the correct answer. Here is how
• Want a reminder to come back and check responses? Here is how to subscribe to a notification
• If you are interested in joining the VM program and help shape the future of Q&A: Here is how you can be part of Q&A Volunteer Moderators

Answer 2

All, I noticed something strange while trying to use parameters in SQL source queries:

The parameters are not parsed when you use the query code editor functionality:

Instead you need to click on the add dynamic code content for the parameters to be parsed properly...

This took me a while to figure out. Is this a bug or intended functionality?

Answer 3

Hi @Dimeji Olayinka ,

Thankyou for using Microsoft Q&A platform and posting your query.

As per your query, it looks like you want to parameterize query your query in the Source transformation of mapping data flow. Here is what you need to do:

• Create parameters in the data flow specifying the correct data type

• In the query option of source transformation , open expression builder and write your query inside double quotes and specify the parameters using string interpolation {$parameter} . For example: "select * from [{$schemaName}].[{$tableName}] where dataTypeName = '{$dataTypeName}'"

• Ouptut in data preview

For more details, please refer the following links:
https://learn.microsoft.com/en-us/azure/data-factory/concepts-data-flow-expression-builder#parameters
https://learn.microsoft.com/en-us/azure/data-factory/parameters-data-flow#passing-in-a-column-name-as-a-parameter
https://learn.microsoft.com/en-us/answers/questions/439041/error-column-operands-are-not-allowed-in-literal-e.html

If the suggested response helped you, Please do consider clicking Accept Answer and take survey for the same as accepted answers help community as well. If you have any further query do let us know.

Answer 4

NOT AN ANSWER. THIS IS A DETAILED QUESTION WITH SCREENSHOTS
Hello @AnnuKumari-MSFT It is not a dynamic parameter if I am giving it a default value. My use case is actually dynamic as I am building the query through the Aggregate and Derive Column schema modifiers and then assigning it to the parameter. I have tried entering the parameter with brackets and quotes around those brackets {$parameter1} "{$parameter1}" as well.

Furthermore, My goal is to keep all this work within 1 dataflow. In the event that the above is not possible I have tried splitting this into a 3 activity pipeline where the 1st activity is a dataflow that outputs my dynamic SQL query, the second is assigning the output to a variable, and the third is a dataflow assigning that variable to the parameter that is used as the SQL query. But when I get to the 3rd activity I get this "Expression cannot be parsed error" which I do not understand. The parameter value is a string it should be easy to parsed. The screenshots are below.