Azure Data Factory读取SharePoint Excel文件失败求助

我在SharePoint中有一个Excel文件，想通过Azure Data Factory读取后写入Azure SQL表。已经完成前置条件，也成功用Web Activity拿到了SharePoint Online的访问令牌，但执行复制活动时出错。操作步骤：

• 创建了基础URL为https://xxx.sharepoint.com/sites/xxx/_api/web/GetFileByServerRelativeUrl('xxx/Shared Documents')/Test01.xlsx的HTTP链接服务
• 创建了数据集
• 创建带复制活动的管道

复制活动抛出的错误包含ExcelUnsupportedFormat和401 Unauthorized，错误栈如下：

ErrorCode=ExcelUnsupportedFormat,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=Only '.xls' and '.xlsx' format is supported in reading excel file while error is ' at System.Threading.Tasks.Task.ThrowIfExceptional(Boolean includeTaskCanceledExceptions) at System.Threading.Tasks.Task.Wait(Int32 millisecondsTimeout, CancellationToken cancellationToken) at Microsoft.DataTransfer.ClientLibrary.MultipartSequentialReadSource.d__5.MoveNext() at Microsoft.DataTransfer.ClientLibrary.TransferStream.ReadInternal(Byte[] buffer, Int32 offset, Int32 count) at Microsoft.DataTransfer.ClientLibrary.TransferStream.Read(Byte[] buffer, Int32 offset, Int32 count) at ICSharpCode.SharpZipLib.Zip.Compression.Streams.InflaterInputBuffer.Fill() at ICSharpCode.SharpZipLib.Zip.Compression.Streams.InflaterInputBuffer.ReadLeByte() at ICSharpCode.SharpZipLib.Zip.Compression.Streams.InflaterInputBuffer.ReadLeInt() at ICSharpCode.SharpZipLib.Zip.ZipInputStream.GetNextEntry() at NPOI.OpenXml4Net.Util.ZipInputStreamZipEntrySource..ctor(ZipInputStream inp) at NPOI.OpenXml4Net.OPC.ZipPackage..ctor(Stream filestream, PackageAccess access) at NPOI.OpenXml4Net.OPC.OPCPackage.Open(Stream in1) at NPOI.Util.PackageHelper.Open(Stream is1) at NPOI.XSSF.UserModel.XSSFWorkbook..ctor(Stream is1) at Microsoft.DataTransfer.ClientLibrary.ExcelUtility.GetExcelWorkbook(String fileExtension, TransferStream stream)'.,Source=Microsoft.DataTransfer.ClientLibrary,''Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=Http request failed with status code 401 Unauthorized, usually this is caused by invalid credentials, please check your activity settings. Request URL: https://xxx.sharepoint.com/sites/xxx/_api/web/GetFileByServerRelativeUrl('xxx/Shared Documents')/Test01.xlsx.,Source=Microsoft.DataTransfer.ClientLibrary,''Type=System.Net.WebException,Message=The remote server returned an error: (401) Unauthorized.,Source=System,'

目标文件位置：

1. 修正HTTP链接服务的API URL #

你的API URL格式错误，导致ADF无法获取到正确的Excel二进制内容，这是ExcelUnsupportedFormat错误的核心原因。正确的URL应该是：
https://xxx.sharepoint.com/sites/xxx/_api/web/GetFileByServerRelativeUrl('/sites/xxx/Shared Documents/Test01.xlsx')/$value

• GetFileByServerRelativeUrl的参数必须是完整的服务器相对路径，格式为/sites/站点名称/文档库名称/文件名
• 必须添加/$value后缀，否则返回的是文件的元数据JSON，而非实际Excel文件的二进制流

2. 确保访问令牌的正确配置 #

• 检查Web Activity获取的令牌是否包含Files.Read或Sites.Read.All权限，且已获得管理员同意
• 在HTTP链接服务中选择Bearer token身份验证，动态传入Web Activity输出的令牌，示例表达式：@activity('获取令牌的Web活动名称').output.access_token
• 配置HTTP请求头，添加Accept: application/octet-stream，确保返回二进制文件流

3. 调整Excel数据集配置 #

• 数据集类型选择Excel，关联修正后的HTTP链接服务
• 在数据集设置中指定正确的工作表名称或数据范围，确认文件确实是有效的.xlsx/.xls格式，没有损坏

4. 排查401未授权问题 #

• 核对Azure AD应用注册的客户端ID、客户端密钥、租户ID是否正确
• 确认应用注册的权限已被管理员授予同意
• 验证文件路径是否准确，当前令牌对应的账号对该Excel文件有读取权限

内容的提问来源于stack exchange，提问作者Mayuran Parathalingam