ADF实现多SQL查询结果写入同一Excel不同工作表的问题

ADF的Copy Data活动不支持将Excel数据集作为Sink目标，这就是你在Sink属性中看不到Excel数据集选项的核心原因。针对你的需求（将3条SQL查询结果写入同一个Excel文件的不同工作表），以下是几个可行的解决方案：

方案一：使用Azure Logic Apps实现 #

• 创建Logic Apps工作流，可选择通过ADF的Web活动触发，或设置定时触发
• 添加SQL Server连接器，配置连接信息后执行第一条查询语句获取结果集
• 添加Excel Online（或OneDrive/SharePoint，取决于Excel文件存储位置）连接器，选择"添加行到表"或"写入范围"操作，指定目标工作表Result1并映射数据字段
• 重复上述SQL查询和Excel写入步骤，分别将另外两条查询结果写入Result2和Result3工作表
• 可将工作表名设为参数，复用操作步骤以提升工作流的可维护性

方案二：使用ADF + Databricks Notebook实现 #

1. 在ADF中创建Databricks链接服务，关联你的Databricks集群
2. 准备参数化的SQL Server数据集（用于存储查询语句参数）和Excel文件所在的存储数据集（如ADLS Gen2）
3. 编写Databricks Notebook脚本，读取SQL查询结果并写入Excel的不同工作表（示例为PySpark代码）：

# 配置SQL Server连接参数
jdbc_url = "jdbc:sqlserver://<你的SQL Server地址>:1433;databaseName=<数据库名称>"
jdbc_properties = {
    "user": "<SQL用户名>",
    "password": "<SQL密码>"
}

# 读取三个SQL查询的结果
query1 = "(SELECT * FROM <表名> WHERE <条件1>) AS result1"
df1 = spark.read.jdbc(url=jdbc_url, table=query1, properties=jdbc_properties)

query2 = "(SELECT * FROM <表名> WHERE <条件2>) AS result2"
df2 = spark.read.jdbc(url=jdbc_url, table=query2, properties=jdbc_properties)

query3 = "(SELECT * FROM <表名> WHERE <条件3>) AS result3"
df3 = spark.read.jdbc(url=jdbc_url, table=query3, properties=jdbc_properties)

# 写入同一个Excel文件的不同工作表
excel_path = "abfss://<容器名>@<存储账户名>.dfs.core.windows.net/<文件路径>/Results.xlsx"

# 写入Result1
df1.write.format("com.crealytics.spark.excel") \
    .option("dataAddress", "Result1!A1") \
    .option("header", "true") \
    .mode("overwrite") \
    .save(excel_path)

# 写入Result2
df2.write.format("com.crealytics.spark.excel") \
    .option("dataAddress", "Result2!A1") \
    .option("header", "true") \
    .mode("overwrite") \
    .save(excel_path)

# 写入Result3
df3.write.format("com.crealytics.spark.excel") \
    .option("dataAddress", "Result3!A1") \
    .option("header", "true") \
    .mode("overwrite") \
    .save(excel_path)

4. 在ADF中添加Databricks Notebook活动，传递SQL查询语句、工作表名、Excel路径等参数，执行Notebook完成数据写入

方案三：使用ADF自定义活动（PowerShell） #

• 创建Azure自动化账户，编写PowerShell脚本实现SQL数据读取和Excel写入逻辑：

# 配置SQL Server连接
$connString = "Server=<SQL Server地址>;Database=<数据库名称>;User ID=<用户名>;Password=<密码>;"

# 执行第一条查询并获取数据
$query1 = "SELECT * FROM <表名> WHERE <条件1>"
$data1 = Invoke-SqlCmd -ConnectionString $connString -Query $query1

# 处理Excel写入（以本地文件为例，若为云存储需先下载再上传）
$excelPath = "<本地临时路径>/Results.xlsx"
$excel = New-Object -ComObject Excel.Application
$excel.Visible = $false
$workbook = $excel.Workbooks.Open($excelPath)

# 写入Result1工作表
$worksheet1 = $workbook.Worksheets.Item("Result1")
$row = 1
foreach ($col in $data1[0].PSObject.Properties.Name) {
    $worksheet1.Cells.Item($row, $col.Index + 1) = $col
}
$row++
foreach ($record in $data1) {
    $colIndex = 1
    foreach ($value in $record.PSObject.Properties.Value) {
        $worksheet1.Cells.Item($row, $colIndex) = $value
        $colIndex++
    }
    $row++
}

# 重复上述逻辑写入Result2和Result3
# ...（省略query2、query3的处理代码）

$workbook.Save()
$workbook.Close()
$excel.Quit()
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($excel) | Out-Null

• 在ADF中创建自定义活动，关联自动化账户和上述PowerShell脚本，传递必要参数后执行

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