天蓝色数据工厂:在查询中使用变量 [英] azure data factory: use variables in query

问题描述

我创建了一个复制活动，该复制活动将数据从内部数据库复制到Azure SQL数据库. 我需要修改dynamiccaly查询，以便它采用一定范围的日期，因此我创建了两个变量:
-暗示
-结束日期
我想在where子句中使用的变量，但是我不知道如何引用变量.我试过了，但是没用:

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')'

请帮助. 谢谢

推荐答案

在管道中(类似于复制"活动)，您将需要使用

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 ''''.

请注意，"@"符号在表达式的开头仅出现一次.另外，要引用管道变量，您将调用"variable s "功能.

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.

这篇关于天蓝色数据工厂:在查询中使用变量的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！