Error while dynamically entering the file path in Copy Data Sink parameter - ADF

Question

I need to dynamically provide the file path in a Copy Data Activity in Azure Data Factory. This is the code that I have written

@concat('PtkUsage/',formatDateTime(pipeline().parameters.windowStart,'yyyy'),'/',formatDateTime(pipeline().parameters.windowStart,'MM'),'/',formatDateTime(pipeline().parameters.windowStart,'dd'))

By using this I am getting the below error that says the character '/' is not expected. So how do I handle this?

{"error":{"code":"BadRequest","message":"ErrorCode=InvalidTemplate, ErrorMessage=The expression 'PtkUsage/formatDateTime(pipeline().parameters.windowStart,'yyyy')/formatDateTime(pipeline().parameters.windowStart,'MM')/formatDateTime(pipeline().parameters.windowStart,'dd')' is not valid: the string character '/' at position '8' is not expected."","target":"","details":null}}

Answer 1

While you're using the Concat function, make sure the construction is correct as every single quote is considered as the start of the string suppose to end with another subsequent single quote but if your expression has a single quote you need to handle that separately.

use ''' for single quotes needed for the expression;

Rewrite this expression carefully, for example;

formatDateTime(pipeline().parameters.windowStart,'yyyy')

AS;

formatDateTime(pipeline().parameters.windowStart,''','yyyy',''',')'

Answer 2

How did you generate the code? Did you use the Expression Builder or type it in manually? The Expression Builder interface is far more reliable, because it manipulates the underlying JSON code properly. Also, the builder experience is more coding friendly with many shortcuts and additional features.

For example, in the following examples I will set two variables url1 and url2. For comparison, I will update them both with the same code [replacing your parameter with utcnow()]:

FIRST VARIABLE

Entered directly in the Value:

Note that the Value box has a black border. This means the text is the literal value and NOT an expression.

SECOND VARIABLE

Entered in the Expression Builder:

Note that the Value box is now BLUE indicating an expression.

And the Expression Builder window for completeness:

THE UNDERLYING JSON You can see the resulting difference if you examine the underlying JSON.

To open the Code view, click on the {} icon in the upper right hand corner:

Viewing the code, we can see that direct entry creates a literal value (including the @s and 's.) Using the Expression Builder, however, the JSON indicates that this is an Expression and not a literal.