ADF / Synapse Pipeline Expression get Substring from a Split function

Question

I am trying to get year month value from file name by using the split and substring function together, for example: "SampleFile_202307.csv". I tried getting the month value by using the Set variable activity, and set a pipeline variable v_test to this value:

@substring(split('SampleFile_202307.csv','_')[1], 4, 2)

But I am getting this error with the split function "Cannot fit unknown into the function parameter string. (6)"

split('SampleFile_202307.csv','_')[1],

Please help me fix this issue. Thank you in advance.

Answer 1

@substring(split('SampleFile_202307.csv','_')[1], 4, 2)

As in above dynamic expression substring function expects String value as a first parameter but we are passing split function because of this it is showing this warning in dynamic expression only when debugging it manages to debug and gave me output.

You can use two set variables to perform this First will split the sting @split('SampleFile_202307.csv','_')[1] and second will get substring from string @substring(variables('year'), 4, 2).

But when I am debugging it manages to debug and gave me output as month value.

Output: