CODEWITHSUNDEEP
azure-data-factory
ΩmegaMan
ΩmegaMan

Reputation: 31606

DataFlow Query Not Replacing Variable

I have a workable sql query

 ... where AcctPD >= '@{variables('StartDate')}'

and want to use a variable such as StartDate value of 202211, a varchar(6).

If I put ... where AcctPD >= '202211' in the sql, the query works, and data is returned. But if I run the above for a variable substitution, I get

at Source 'XXXXx': Parse error at line: 36, column: 41: Incorrect syntax near 'StartDate'. SQL Server error encountered while reading from the given table or while executing the given query.

Why?

enter image description here

Upvotes: 0

Views: 806

Answers (1)

Aswin
Aswin

Reputation: 7126

  • ADF pipeline variables and pipeline parameters cannot be used directly in dataflow. In order to use the value of pipeline variable, a dataflow parameter is to be created and value of the pipeline variable should be passed to that dataflow parameter. I tried to repro this.

  • Pipeline variable StartDate is created and value is assigned.

enter image description here

  • Dataflow Query is run without using the variable. Data is read from the SQL table without any error.

gif53

  • When variable is given in the Query, same error is produced. gif51

  • In order to solve this, dataflow parameter named par_StartDate is created.

enter image description here

  • In Source transformation Query, open expression builder is selected.

enter image description here

  • Query is written as "select * from Target_merged_table where createdat='{$par_StartDate}'" in dataflow expression builder.

enter image description here

enter image description here

  • Sink transformation is added with csv file as a sink dataset.

  • This dataflow is added in dataflow activity of pipeline. Value of pipeline variable StartDate is passed to dataflow parameter par_StartDate. gif51

  • When pipeline is run, dataflow is executed successfully.

Upvotes: 2

Related Questions