azure data factory: use variables in query

user_6093154 0
Viewed 7313

I have created a copy activity that copies data from an on premise database to a Azure SQL Database. I need to modify dynamiccaly the query so it take a range of date, so I create two variables:
- inidate
- enddate
that I want to use inside the where clause, but I don't know how to reference the variables. I tried this but it doesn't work:

"SELECT * FROM tableOnPrem WHERE dateOnPrem BETWEEN '@variable('inidate')' AND '@variable('enddate')'

please help. meny thanks

azure-data-factory
3 Answers

I found that adding curly braces was enough to get this working (and like Joel Cochran stated using the plural variables keyword):

SELECT * FROM tableOnPrem WHERE dateOnPrem BETWEEN '@{variables('inidate')}' AND '@{variables('enddate')}'

However, it still reports an error on the Source "Preview data" option, but despite this it runs correctly in the actual pipeline run.

user_67225830

In a Pipeline (like for Copy activity), you will need to build the query string dynamically using the Pipeline Expression Language (PEL). The best way to do this is to use the concat function to piece together the query:

@concat('SELECT * FROM tableOnPrem WHERE dateOnPrem BETWEEN ''',variables('inidate'),''' AND ''',variables('enddate'),'''')

This can get complex rather quickly, so you'll need to pay extra attention to commas, parentheses, ''' and ''''.

Note that the '@' symbol only appears once, at the beginning of the expression. Also, to reference a pipeline variable you are calling the "variables" function.

user_758380

Another option to handle is define them as pipeline parameters pipeline-prameters. Say for example if you have parameters defined as

  • start_date
  • end_date

you can have query written like below

SELECT * FROM tableOnPrem WHERE dateOnPrem BETWEEN @{pipeline().parameters.start_date} AND @{pipeline().parameters.end_date}
user_2971130
Related