I want to copy data from SharePoint using Synapse's Copy Activity HTTP link service.

I want to put an expression using a function in the formula (1) at the URL of the source data set, but it is not working as expected.

Please let me know how I can fix this. (1)https://sharepoint.com/teams/Data/Share/Planning/_api/web/GetFileByServerRelativeUrl('/teams/Data/Share/Planning/Shared Documents/05_master/502_outlet_list/2022/2022_monthly_master/outlet_info_20221101_Outlet List _.xlsx')/$value

Here is the statement that results in an error (2)@concat('https://sharepoint.com/teams/Data/Share/Planning/_api/web/GetFileByServerRelativeUrl('/teams/Data/Share/Planning/_api/web/GetFileByServerRelativeUrl('/teams/Data/Share/Planning/Shared Documents/05_master/502_outlet_list/',formatDateTime(addHours(utcNow(),9),'yyyy'),'/',formatDateTime(addHours(utcNow(),9),'yyyy'),'_monthly_master/outlet_info_',formatDateTime(addHours(utcNow(),9),'yyyyMMdd'),'_Outlet List _.xlsx')/$value')

CodePudding user response：

• The error is because of the ambiguous usage of single quotes inside concat function. Instead, you can use string interpolation @{...}.

• You can replace your dynamic content with the below dynamic content which makes use of string interpolation. I have used this in a set variable activity for demonstration.

https://sharepoint.com/teams/Data/Share/Planning/_api/web/GetFileByServerRelativeUrl('/teams/Data/Share/Planning/Shared Documents/05_master/502_outlet_list/@{formatDateTime(addHours(utcNow(),9),'yyyy')}/@{formatDateTime(addHours(utcNow(),9),'yyyy')}_monthly_master/outlet_info_@{formatDateTime(addHours(utcNow(),9),'yyyyMMdd')}_Outlet List _.xlsx')/$value

• This would give the result as shown below:

• You can modify the formatDateTime() function as per your requirement where you can use 2022-11-01 instead of utcNow().