CODEWITHSUNDEEP
azureazure-data-factoryazure-synapse
BackInBlack
BackInBlack

Reputation: 99

querying of SQL table with dynamic date filter in Azure data factory (Azure synapse analytics)

How to pass datetime parameter to a SQL query in a source of the data flow activity in ADF/synapse analytics?

I am building a synapse analytics pipeline that performs a delta load in a fact table. First, the table is queried with a lookup activity to get the latest LoadDate value. The returned value is then set as a variable and passed as a parameter to a data flow activity.

pipeline1

I am struggling to get the data flow running properly. I have tried to concatenate the SQL query with the filter value in the 'SetVariable' activity but get 'The store configuration is not defined.' error. Same happens when I pass only converted LoadDate value to the source query in data flow activity:

"SELECT top 10 * FROM dbo.facts WHERE timestamp > @pipeline().parameters.LastLoadedDate"

Upvotes: 1

Views: 2242

Answers (2)

BackInBlack
BackInBlack

Reputation: 99

After many try-and-error attempts, this syntax worked for me:

concat("SELECT * FROM dbo.facts WHERE timestamp > CONVERT(datetime2, '" , $LastLoadedDate, "')")

the key was to use double quotes to wrap concatenated strings...

Upvotes: 2

Steve Johnson
Steve Johnson

Reputation: 8660

Please try this SQL:

concat('select top 10 * FROM dbo.facts WHERE timestamp >', $yourParameterName)

In data flow, you can't use pipeline expression like this @pipeline().parameters.LastLoadedDate, you should use the parameter value in data flow.

Upvotes: 0

Related Questions