如何通过Azure Data Factory动态将ADLS多Excel多工作表导入Azure SQL

以下是实现动态遍历ADLS中所有Excel文件、每个文件的工作表，并在Azure SQL自动创建对应表的完整步骤：

1. 基础准备 #

• 配置好ADLS Gen2（或Gen1）与Azure SQL的链接服务，确保ADF有权限访问这两个资源。
• 创建两个数据集：
  • Excel数据集：指向ADLS的目标文件夹，不指定具体工作表，并勾选“第一行作为表头”（如果你的工作表有表头）。
  • Azure SQL数据集：指向目标数据库，不指定具体表，后续用动态内容填充。

2. 获取所有Excel文件列表 #

添加Get Metadata活动（命名为GetAllExcelFiles）：

• 数据源选择上述Excel数据集，路径设置为存放Excel文件的ADLS容器/文件夹。
• 在“字段”选项中勾选Child Items，活动会返回该路径下所有文件的元数据（包括文件名、路径等）。
• 可选：添加过滤逻辑，只保留.xlsx/.xls文件，比如在后续For Each中用条件判断：@equals(item().type, 'File') and or(endswith(item().name, '.xlsx'), endswith(item().name, '.xls'))

3. 外层For Each循环：遍历每个Excel文件 #

添加For Each活动（命名为LoopThroughExcelFiles）：

• 输入项设置为：@activity('GetAllExcelFiles').output.childItems
• 循环内部添加第二个Get Metadata活动，用于获取当前文件的所有工作表。

4. 获取当前Excel文件的工作表列表 #

在LoopThroughExcelFiles内部添加Get Metadata活动（命名为GetSheetNames）：

• 数据源选择Excel数据集，文件路径用动态内容：@item().path（引用外层循环当前遍历的文件路径）。
• 在“字段”选项中勾选Child Items，此时活动会返回该Excel文件下所有工作表的名称列表（每个子项的name字段就是工作表名）。

5. 内层For Each循环：遍历每个工作表并同步到SQL #

在GetSheetNames之后添加For Each活动（命名为LoopThroughSheets）：

• 输入项设置为：@activity('GetSheetNames').output.childItems
• 循环内部添加Copy Data活动，配置如下：
  • 源设置：
    • 数据源选择Excel数据集，文件路径用@item().path（外层循环的当前文件路径），工作表用@item().name（内层循环的当前工作表名）。
  • 目标设置：
    • 数据源选择Azure SQL数据集，表名用动态内容生成（避免SQL命名冲突），比如：@concat('dbo.', replace(item().name, ' ', '_'))（将工作表名中的空格替换为下划线，前缀加dbo schema）。
    • 勾选自动创建表选项（在复制活动的“目标”设置中，或SQL数据集的高级设置里），ADF会根据Excel工作表的结构自动在SQL中创建对应表。

关键注意事项 #

• 命名规范处理：如果工作表名包含空格、特殊字符（如-、#），一定要用replace()或regexReplace()函数转换为SQL允许的标识符，避免创建表失败。
• 并行度调整：外层For Each的并行度默认是20，若文件数量过多，可适当降低并行度，避免触发ADLS或SQL的限流。
• 错误处理：可在每个活动后添加On Failure分支，将错误信息（如文件名、工作表名、错误原因）写入SQL日志表，方便后续排查。
• 表头验证：确保Excel工作表的表头是规范的，避免自动创建的SQL表字段名出现异常。

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