如何在SSIS中实现基于交货日期的动态列数Excel目标？

针对你这种每周动态生成、交货日期数量不固定的报表需求，我分SQL端动态行转列和Excel动态导出两部分给你解决方案：

第一步：SQL Server端生成动态列结果 #

首先需要把每个产品的多行交货记录转成一行多列的格式，列数根据最大的交货次数自动调整。我们用CTE加动态SQL来实现：

1. 给交货记录添加序号 #

先用ROW_NUMBER()给每个产品的交货记录按日期排序编号：

WITH RankedDeliveries AS (
    SELECT 
        PRODUCT,
        QUANTITY_EXPECTED,
        DELIVERY_DATE,
        -- 按产品分组，交货日期升序编号
        ROW_NUMBER() OVER (PARTITION BY PRODUCT ORDER BY DELIVERY_DATE) AS DeliverySeq
    FROM YourTableName -- 替换成你的实际表名
)

2. 动态生成列与最终查询 #

接下来动态拼接列名（Q_EXP_1、DELIVERY_DATE_1...），然后执行动态查询：

DECLARE @MaxSeq INT;
-- 获取所有产品中最大的交货次数
SELECT @MaxSeq = MAX(DeliverySeq) FROM RankedDeliveries;

DECLARE @ColumnSQL NVARCHAR(MAX) = '';
DECLARE @i INT = 1;

-- 循环生成每个序号对应的数量和日期列
WHILE @i <= @MaxSeq
BEGIN
    SET @ColumnSQL += CONCAT(
        ', MAX(CASE WHEN DeliverySeq = ', @i, ' THEN QUANTITY_EXPECTED END) AS Q_EXP_', @i,
        ', MAX(CASE WHEN DeliverySeq = ', @i, ' THEN CONVERT(VARCHAR(10), DELIVERY_DATE, 103) END) AS DELIVERY_DATE_', @i
    );
    SET @i += 1;
END

-- 构建并执行最终查询
DECLARE @FinalSQL NVARCHAR(MAX) = CONCAT(
    'SELECT PRODUCT', @ColumnSQL, '
     FROM RankedDeliveries
     GROUP BY PRODUCT'
);

EXEC sp_executesql @FinalSQL;

这里用CONVERT(VARCHAR(10), DELIVERY_DATE, 103)把日期转成DD/MM/YYYY的字符串格式，避免Excel自动识别日期时出现格式问题。

执行后就能得到和你期望完全一致的动态列结果。

第二步：导出到动态列的Excel文件 #

因为列数是每周变化的，不能用固定模板，推荐以下三种实用方案：

方案一：PowerShell自动生成报表（推荐用于定时任务） #

写一个PowerShell脚本，每周定时执行（用Windows任务计划），自动从SQL拉取动态数据并生成Excel：

# 配置数据库参数
$serverName = "你的SQL服务器名"
$databaseName = "你的数据库名"
$tableName = "你的表名"

# 嵌入动态SQL查询
$query = @"
WITH RankedDeliveries AS (
    SELECT 
        PRODUCT,
        QUANTITY_EXPECTED,
        DELIVERY_DATE,
        ROW_NUMBER() OVER (PARTITION BY PRODUCT ORDER BY DELIVERY_DATE) AS DeliverySeq
    FROM $tableName
)
DECLARE @MaxSeq INT;
SELECT @MaxSeq = MAX(DeliverySeq) FROM RankedDeliveries;

DECLARE @ColumnSQL NVARCHAR(MAX) = '';
DECLARE @i INT = 1;
WHILE @i <= @MaxSeq
BEGIN
    SET @ColumnSQL += CONCAT(
        ', MAX(CASE WHEN DeliverySeq = ', @i, ' THEN QUANTITY_EXPECTED END) AS Q_EXP_', @i,
        ', MAX(CASE WHEN DeliverySeq = ', @i, ' THEN CONVERT(VARCHAR(10), DELIVERY_DATE, 103) END) AS DELIVERY_DATE_', @i
    );
    SET @i += 1;
END

DECLARE @FinalSQL NVARCHAR(MAX) = CONCAT(
    'SELECT PRODUCT', @ColumnSQL, '
     FROM RankedDeliveries
     GROUP BY PRODUCT'
);

EXEC sp_executesql @FinalSQL;
"@

# 执行SQL查询获取数据
Install-Module SqlServer -Force -Scope CurrentUser # 第一次运行需要安装模块
$data = Invoke-SqlCmd -ServerInstance $serverName -Database $databaseName -Query $query

# 创建Excel文件并写入数据
$excel = New-Object -ComObject Excel.Application
$excel.Visible = $false
$workbook = $excel.Workbooks.Add()
$worksheet = $workbook.Worksheets.Item(1)

# 写入表头
$colIndex = 1
foreach ($col in $data.Columns) {
    $worksheet.Cells.Item(1, $colIndex) = $col.ColumnName
    $colIndex++
}

# 写入数据行
$rowIndex = 2
foreach ($row in $data) {
    $colIndex = 1
    foreach ($col in $data.Columns) {
        $worksheet.Cells.Item($rowIndex, $colIndex) = $row.$($col.ColumnName)
        $colIndex++
    }
    $rowIndex++
}

# 保存并清理
$outputPath = "C:\报表路径\每周交货报表_$(Get-Date -Format 'yyyyMMdd').xlsx"
$workbook.SaveAs($outputPath)
$workbook.Close()
$excel.Quit()

# 清理COM对象，避免Excel进程残留
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($worksheet) | Out-Null
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($workbook) | Out-Null
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($excel) | Out-Null
[GC]::Collect()
[GC]::WaitForPendingFinalizers()

方案二：Excel Power Query手动/自动刷新 #

如果不需要完全自动化，用Excel的Power Query可以快速实现：

1. 打开Excel → 数据 → 获取数据 → 从数据库 → 从SQL Server数据库
2. 输入服务器和数据库名，点击「高级选项」，把上面的动态SQL粘贴到「SQL语句」框中
3. 点击确定，加载数据到Excel。每次需要刷新时，右键点击数据区域 → 刷新，Power Query会自动重新执行SQL并调整列数。

方案三：SSIS脚本任务动态导出 #

如果你熟悉SSIS，可以通过脚本任务动态处理元数据：

1. 用Execute SQL Task执行动态SQL，把结果集保存到对象变量
2. 添加脚本任务，读取变量中的元数据（列名、数量），动态创建Excel目标的列映射
3. 执行Data Flow把数据导入Excel。这种方法适合企业级ETL流程，但需要编写C#/VB脚本处理动态元数据。

注意事项 #

• 动态SQL的安全性：因为所有变量都是从自身表生成的，没有用户输入，所以不存在SQL注入风险
• 日期格式：如果Excel中日期显示异常，确保SQL中用CONVERT转成明确的字符串格式
• PowerShell脚本：第一次运行需要安装SqlServer模块，且要注意清理COM对象避免进程残留

内容的提问来源于stack exchange，提问作者A.L