CODEWITHSUNDEEP
parameterscopylookupazure-data-factory
jbazelmans
jbazelmans

Reputation: 303

Azure Data Factory use result from lookup in filename DataSink

The idea is to use a lookup-activity to select the first mentioned month and year in a table. 

select MONTH(regtime) as FirstMonth , YEAR(RegTime) as Year from MyTable where ID = (select MIN(id) from MyTable)

Then the result for this lookup is used in the copy-activity to select the rows in this table that have a timestamp with the month and year selected in the lookup activity. This can be defining the source in the copy activity as follows:

select * from MyTable where MONTH(RegTime) = '@{activity('LookupFirstMonth').output.firstRow.FirstMonth}' and YEAR(RegTime) = '@{activity('LookupFirstMonth').output.firstRow.Year}'

The next step should be to copy the rows to a data lake. In the Sink area of the copy-activityit is possible to dynamcly set the filename for the file where the data gets put in to. But I can't find out how to do it the I want it. I want the filename to reflect the month and year, selected in the lookup activity.

I can set the filename of the Sink to @{activity('LookupFirstMonth').output.firstRow.MONTH}, and then the filename will be the monthnumber. But that is not enough. It also should have the YEAR in the filename. When I conifure the filename as:

@CONCAT(@{activity('LookupFirstMonth').output.firstRow.FirstMonth}, @{activity('LookupFirstMonth').output.firstRow.Year}, '.txt')

I am getting an error:

 {"code":"BadRequest","message":"The expression 'CONCAT(@{activity('LookupFirstMonth').output.firstRow.FirstMonth}, @{activity('LookupFirstMonth').output.firstRow.Year}, '.txt')' is not valid: the string character '@' at position '7' is not expected.\"","target":"pipeline/pipeline1/runid/3cf0a5a9-01df-494e-bdaf-dfc66f406a83"}

So how do I use the outcome of the lookup activity in a filename for the Sink in the copy activity

Upvotes: 0

Views: 3264

Answers (2)

Fang Liu
Fang Liu

Reputation: 2363

The right expression should be

@CONCAT(activity('LookupFirstMonth').output.firstRow.FirstMonth, activity('LookupFirstMonth').output.firstRow.Year, '.txt')

Or

@{CONCAT(activity('LookupFirstMonth').output.firstRow.FirstMonth, activity('LookupFirstMonth').output.firstRow.Year, '.txt')}

The first one is a pure expression, while the second one is string interpolation (with {}）

Upvotes: 1

DraganB
DraganB

Reputation: 1138

Basically if you start your expression with @concat, everything inside concat function cant start with @. so try this: 

@CONCAT({activity('LookupFirstMonth').output.firstRow.FirstMonth}, {activity('LookupFirstMonth').output.firstRow.Year}, '.txt')

example from msdn documentation: 

"@concat('Answer is: ', string(pipeline().parameters.myNumber))"

so you see, before pipeline() there is no @.

Upvotes: 0

Related Questions