如何通过SSIS加载指定每日新增的两类平面文件至SQL Server表？

嘿，这个需求我太熟了——每天从一堆文件里精准揪出当天的那两个特定格式文件加载到SQL Server，对吧？我之前帮团队处理过几乎一模一样的场景，给你几个实用的方案，你可以根据自己的工具偏好选：

既然你提到了Foreach循环，那先说说SSIS里最稳妥的做法：别用宽泛的通配符，直接动态生成当天的目标文件名，精准定位。

• 第一步：定义变量

  • 先建一个日期格式变量@[User::CurrentFileNameDate]，表达式根据你的文件名日期格式来写。比如你的例子里是MM_DD_YYYY（比如1_12_2017是1月12日），表达式可以写：

    (DT_WSTR,2)DATEPART("mm", GETDATE()) + "_" + (DT_WSTR,2)DATEPART("dd", GETDATE()) + "_" + (DT_WSTR,4)DATEPART("yyyy", GETDATE())
    

    如果文件名里的月份/日期是两位带前导零的（比如01_12_2017），就改成：

    RIGHT("0" + (DT_WSTR,2)DATEPART("mm", GETDATE()),2) + "_" + RIGHT("0" + (DT_WSTR,2)DATEPART("dd", GETDATE()),2) + "_" + (DT_WSTR,4)DATEPART("yyyy", GETDATE())
    

  • 再建两个文件名变量：
    • @[User::NonPaymentFilePath] = "C:\Your\Folder\Path\Non_Payment_Stat_Data" + @[User::CurrentFileNameDate] + ".txt"
    • @[User::PaymentFilePath] = "C:\Your\Folder\Path\Payment_Stat_Data" + @[User::CurrentFileNameDate] + ".txt"

• 第二步：检查文件是否存在

  • 用两个File System Task，分别检查上面两个变量对应的文件是否存在。如果存在，才执行后续的加载逻辑（比如Flat File Source → OLE DB Destination）；如果不存在，可以记录日志或者直接跳过。

• 为啥不用Foreach循环？因为文件夹里有几千个文件，Foreach遍历所有文件再筛选太浪费资源，精准定位效率高多了，还不会误匹配其他类似开头的无关文件。

如果你更喜欢用脚本，或者想把任务完全自动化，PowerShell是个好选择，配合SQL Agent Job每天定时跑就行：

# 配置基础参数
$folderPath = "C:\Your\Target\Folder\"
$sqlServer = "YourSQLServerName"
$sqlDB = "YourDatabaseName"
$nonPaymentTable = "dbo.Non_Payment_Stat"
$paymentTable = "dbo.Payment_Stat"

# 生成当天的日期字符串（匹配文件名格式）
# 这里按你的例子用MM_DD_YYYY，需要的话改格式
$currentDate = Get-Date -Format "MM_dd_yyyy"

# 拼接目标文件名
$nonPaymentFile = Join-Path -Path $folderPath -ChildPath "Non_Payment_Stat_Data$currentDate.txt"
$paymentFile = Join-Path -Path $folderPath -ChildPath "Payment_Stat_Data$currentDate.txt"

# 加载Non_Payment文件
if (Test-Path -Path $nonPaymentFile) {
    $bulkInsertQuery = @"
    BULK INSERT $sqlDB.$nonPaymentTable
    FROM '$nonPaymentFile'
    WITH (
        FIELDTERMINATOR = '|', -- 替换成你的文件分隔符
        ROWTERMINATOR = '\n',
        FIRSTROW = 2, -- 如果文件有表头就写2，没有写1
        CODEPAGE = '65001' -- UTF-8编码，根据实际情况调整
    )
"@
    try {
        Invoke-SqlCmd -ServerInstance $sqlServer -Query $bulkInsertQuery
        Write-Host "✅ Non_Payment文件加载成功：$nonPaymentFile"
    } catch {
        Write-Host "❌ 加载Non_Payment文件失败：$($_.Exception.Message)"
    }
} else {
    Write-Host "ℹ️ 未找到当天的Non_Payment文件：$nonPaymentFile"
}

# 加载Payment文件（逻辑和上面一致）
if (Test-Path -Path $paymentFile) {
    $bulkInsertQuery = @"
    BULK INSERT $sqlDB.$paymentTable
    FROM '$paymentFile'
    WITH (
        FIELDTERMINATOR = '|',
        ROWTERMINATOR = '\n',
        FIRSTROW = 2,
        CODEPAGE = '65001'
    )
"@
    try {
        Invoke-SqlCmd -ServerInstance $sqlServer -Query $bulkInsertQuery
        Write-Host "✅ Payment文件加载成功：$paymentFile"
    } catch {
        Write-Host "❌ 加载Payment文件失败：$($_.Exception.Message)"
    }
} else {
    Write-Host "ℹ️ 未找到当天的Payment文件：$paymentFile"
}

把这个脚本保存成.ps1文件，然后在SQL Agent里建一个Job，每天定时执行就行——比SSIS更轻量，维护起来也简单。

要是你一定要保留Foreach循环的思路，那也可以优化一下，避免误匹配：

• 先在SSIS里定义好当天的日期变量（和方案1里的一样）
• 把Foreach循环的文件枚举器设置为*_$currentDate.txt（用表达式生成通配符），这样只会遍历带当天日期的文件
• 在Foreach循环内部，加一个脚本任务，判断当前遍历到的文件名是否以Non_Payment_Stat_Data或Payment_Stat_Data开头：
  • 脚本里可以用Path.GetFileName(Dts.Variables["User::CurrentFileName"].Value.ToString())获取文件名，然后用StartsWith方法判断
  • 只有符合条件的文件，才继续执行加载逻辑；不符合的直接跳过

不过这个方法还是会遍历所有带当天日期的文件，效率不如方案1高，适合文件夹里当天的文件不多的情况。

• 日期格式一定要匹配：仔细核对文件名里的日期是MM_DD_YYYY还是DD_MM_YYYY，有没有前导零，错一个字符就找不到文件
• 文件编码别忽略：如果你的文件是UTF-8或者其他编码，一定要在SSIS的Flat File Connection Manager或者PowerShell的BULK INSERT里设置正确的编码，不然会出现乱码
• 加日志记录：不管用哪种方法，都要记录加载状态（成功/失败/未找到），后续排查问题会省很多事

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