ADF -pass SQL query in source with single quotes in date column

user_8940801 0
Viewed 1359

I am trying to pass parameters in dynamic pipeline in source query inside data flow. Tablename and Column name is dynamic and will be passed through parameters. But i got an error while executing the below ADF query:

ADF Query: concat('SELECT ', $ValidationColumn, ' FROM ', $ValidationTable,' WHERE audit_datetime >= ', replace($StartAuditDateTime,'T',''),' AND audit_datetime <= ' ,replace($EndAuditDateTime,'T','') )

Expected SQL query: Select ID from Master where audit_datetime >= '2020-07-23 18:47:20.5666' AND audit_datetime <= '2020-07-24 01:47:20.5456'

azure-data-factory-2 azure-data-factory azure-sql-database
3 Answers

Please try below expression to form your query in your ADF Mapping dataflow expression builder.

"SELECT '{$ValidationColumn}' FROM '{$ValidationTable}' WHERE audit_datetime >= '{$StartAuditDateTime}' AND audit_datetime <= '{$EndAuditDateTime}'"

This will result in below QUERY formation:

SELECT ID FROM Master WHERE audit_datetime >= '2020-07-23 18:47:20.5666' AND audit_datetime <= '2020-07-24 01:47:20.5456'
user_114491800

I am assuming you want to escape single quote to get '{yourDateValue}' format, you can do that like this ''''

example:

@{concat('select * from [yourtable] WHERE [yourColumn] >=','''',{yourexpression},'''')}
user_75937750

Use this SQL syntax in Expression builder in ADF Data Flow where you need to use singe quotes

"SELECT * FROM myTable WHERE someColumn = 'Y'"
user_11457120
Related