如何通过SSIS作业获取未提交月度文件的企业列表？

步骤1：维护预期企业列表 #

先在SQL Server创建存储预期提交企业的表，后续企业变动直接维护该表即可：

CREATE TABLE dbo.ExpectedCompanies
(
    CompanyName NVARCHAR(100) PRIMARY KEY,
    IsActive BIT DEFAULT 1
);

-- 插入示例企业数据
INSERT INTO dbo.ExpectedCompanies (CompanyName)
VALUES ('AlphaCO'), ('BetaCO'), ('DeltaCO'), ('ZetaCO');

步骤2：获取目录内已提交文件列表 #

利用Task Factory的Directory List Task（比原生组件更灵活）实现文件扫描：

• 新建SSIS变量：
  • @CurrentDate（DateTime类型）：表达式GETDATE()，用于生成对应日期的文件名匹配规则
  • @FilePattern（String类型）：表达式"*_File_" + (DT_WSTR(4)YEAR(@[User::CurrentDate]) + RIGHT("0" + (DT_WSTR(2)MONTH(@[User::CurrentDate])),2) + RIGHT("0" + (DT_WSTR(2)DAY(@[User::CurrentDate])),2))，自动生成*_File_yyyymmdd格式的匹配通配符
  • @SourceFolder（String类型）：设置为文件接收的目标目录路径
  • @FileList（Object类型）：用于存储扫描到的文件列表结果集
• 配置Directory List Task：
  • 源目录选择@SourceFolder变量
  • 文件过滤选择@FilePattern变量
  • 结果集选择「Full Result Set」，映射到@FileList变量

步骤3：提取已提交企业名称 #

添加Data Flow Task处理文件列表，提取已提交的企业名称：

1. 数据流内添加Recordset Source，选择@FileList变量，勾选FileName列
2. 添加Derived Column转换，从文件名中截取企业名称：
   • 表达式：SUBSTRING(FileName, 1, FINDSTRING(FileName, "_File_", 1) - 1)
   • 输出列命名为SubmittedCompany
3. 添加OLE DB Destination，将SubmittedCompany写入临时表（运行前先清空）：

   -- 临时表创建语句
   CREATE TABLE dbo.SubmittedCompaniesTemp
   (
       SubmittedCompany NVARCHAR(100)
   );
   -- 每次运行前清空临时表，可通过Execute SQL Task执行
   TRUNCATE TABLE dbo.SubmittedCompaniesTemp;
   

步骤4：对比生成未提交企业列表 #

通过Execute SQL Task对比预期与已提交企业，结果可存入变量或日志表：

• 存入变量（用于邮件任务）：
  SQL语句：

  SELECT STRING_AGG(CompanyName, ', ') AS MissingCompanies
  FROM dbo.ExpectedCompanies
  WHERE IsActive = 1
  AND CompanyName NOT IN (SELECT SubmittedCompany FROM dbo.SubmittedCompaniesTemp);
  

  结果集选择「Single Row」，映射到@MissingCompanies（String类型）变量
• 存入日志表（长期记录）：
  先创建日志表：

  CREATE TABLE dbo.MissingCompanyLog
  (
      LogID INT IDENTITY(1,1) PRIMARY KEY,
      RunDate DATETIME DEFAULT GETDATE(),
      MissingCompany NVARCHAR(100)
  );
  

  执行插入语句：

  INSERT INTO dbo.MissingCompanyLog (MissingCompany)
  SELECT CompanyName
  FROM dbo.ExpectedCompanies
  WHERE IsActive = 1
  AND CompanyName NOT IN (SELECT SubmittedCompany FROM dbo.SubmittedCompaniesTemp);
  

步骤5：邮件任务中使用未提交企业列表 #

添加Send Mail Task，配置邮件服务器后，在邮件正文引用@MissingCompanies变量，示例正文：

本月未提交文件的企业：@[User::MissingCompanies]

注意事项 #

• 若文件日期不是运行当天（比如每月固定日期），调整@CurrentDate的表达式，例如取上月最后一天：DATEADD(day, -1, DATEADD(month, DATEDIFF(month, 0, GETDATE()) + 1, 0))
• 确保SSIS作业运行账户拥有文件目录访问权限和SQL Server读写权限
• Task Factory的Directory List Task支持排除临时文件等复杂过滤规则，可按需配置

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