SSIS中如何拆分单行数据插入存在外键关联的两个SQL Server表

针对你的场景有两种成熟的实现方式，分别对应简单需求和高性能需求：

方案1：轻量简单实现（适合中小数据量） #

不需要额外编写脚本，仅用SSIS原生组件即可实现：

• 你已经通过脚本组件解析出完整行数据，在数据流中添加OLE DB命令组件，连接到目标SQL Server实例，输入命令文本：

INSERT INTO Person (Name, UploadedBy, DateAdded)
OUTPUT INSERTED.ID
VALUES (?, ?, ?)

• 将解析得到的前3列依次映射到上述SQL的3个参数，在OLE DB命令的输出列中将返回的INSERTED.ID重命名为NewPersonId
• 后续添加OLE DB目标组件，选择PersonFile表，将NewPersonId映射到PersonId字段，解析得到的最后一列映射到PersonFile字段即可

该方案逻辑直观，配置成本极低，缺点是OLE DB命令为逐行执行，数据量过大时性能较差。

方案2：高性能批量实现（适合150MB大文件场景） #

通过中间staging表实现全批量操作，性能远高于逐行处理：

1. 提前在SQL Server中创建中间导入表，用于暂存解析后的全量数据：

CREATE TABLE Staging_PersonImport (
    RowId INT IDENTITY(1,1) PRIMARY KEY,
    Name varchar(100),
    UploadedBy varchar(50),
    DateAdded datetime,
    PersonFile varchar(max)
)

2. 数据流中直接将解析后的全量字段批量插入该中间表，开启OLE DB目标的「快速加载」模式，150MB文件通常几秒即可完成写入
3. 数据流结束后添加「执行SQL任务」，执行批量插入语句：

DECLARE @InsertedPerson TABLE (
    ID INT,
    RowId INT
)

-- 批量插入Person表，关联返回生成的ID和中间表行号
INSERT INTO Person (Name, UploadedBy, DateAdded)
OUTPUT INSERTED.ID, inserted.RowId INTO @InsertedPerson
SELECT Name, UploadedBy, DateAdded, RowId FROM Staging_PersonImport

-- 批量关联插入PersonFile表
INSERT INTO PersonFile (PersonId, PersonFile)
SELECT ip.ID, spi.PersonFile
FROM @InsertedPerson ip
JOIN Staging_PersonImport spi ON ip.RowId = spi.RowId

-- 清空中间表以备下次使用
TRUNCATE TABLE Staging_PersonImport

该方案没有逐行处理的开销，性能最优，也完全满足外键约束的要求，调试和维护成本都很低。

你之前考虑的PowerShell、存储过程方案也可以实现，但如果已经在SSIS流程框架内，上述两种方案的适配性和可维护性更高，表值参数存储过程的性能和中间表方案接近，但配置更复杂。

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