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 takes a range of dates, with these two variables:
inidateenddateWhich 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')'
How can this be achieved?
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.
Another option to handle is define them as pipeline parameters pipeline-prameters. Say for example if you have parameters defined as
you can have query written like below
SELECT * FROM tableOnPrem WHERE dateOnPrem BETWEEN @{pipeline().parameters.start_date} AND @{pipeline().parameters.end_date}
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.
enddate contains '00010101'; drop table users;-- ? Or '"potato" ? Or 31.12.2020 ?