这是一个使用“SSIS 2008 R2”创建的示例SSIS包，演示了你想要实现的内容。样例包将传入文件数据加载到临时表中。然后，使用SQL Server的“Rank”函数和“Partition”子句，按照你的要求加载数据。这个样例假定你的数据库版本为“SQL Server 2005或更高版本”。由于没有提供确切的XML文件格式，所以我使用CSV文件格式作为输入文件。
我们来创建两个名为“Source_001.csv”和“Source_002.csv”的CSV文件。创建两个文件仅是为了展示包的逻辑是否正常工作。
在SQL Server数据库中，创建目标表“dbo.CategoryName”。这是最终加载数据的表。它具有基于列“Id”和“CatType”的组合键。
在SQL Server数据库中，创建临时表“dbo.CategoryName_Staging”。这是将文件数据临时加载到的表。这个暂存表的结构模拟传入文件的结构。
在SQL Server数据库中，使用本文底部提供的存储过程脚本创建名为“dbo.PopulateDestination”的存储过程。该存储过程使用“RANK”函数与“PARTITION”子句结合使用，以识别应分配给CatType值的正确Id。
创建一个新的SSIS包。创建一个名为“SQLServer”的OLE DB连接管理器。这将指向你的SQL Server数据库。创建一个名为“Source”的平面文件连接管理器。
将平面文件连接管理器配置如下所示。我将源csv文件放在路径“C:\temp\”中。
在“平面文件连接管理器编辑器”的“高级”选项卡上，重命名列信息。LegacyId - 整数，CatType - 字符串（10），Name - 字符串（30），数字表示OutputColumnWidth。
在SSIS包的控制流选项卡上，放置一个“Execute SQL task”，然后是“Data Flow Task”，然后是另一个“Execute SQL task”。
双击第一个“Execute SQL task”，配置它截断临时表。
双击“Data Flow Task”以切换到数据流选项卡。在数据流选项卡中，放置一个Flat File Source管理器来读取CSV文件，并放置一个OLE DB Destination来将数据写入临时表。
如下所示配置Flat file source，使用flat file connection manager读取flat file source。
如下所示配置OLE DB destinaton，接受传入数据并写入目标文件。
返回控制流选项卡，配置最后一个Execute SQL task，以调用新创建的存储过程。包开发现在已经完成。
执行该包。请记住，该包仅配置为仅读取第一个源文件Source_001.csv。在包执行后，查询目标表CategoryName，以查找以下数据。
现在，停止SSIS包的执行，双击名为
Source的平面文件连接管理器。将文件名路径更改为
Source_002.csv以读取第二个文件。
执行包。现在，该包已配置为读取第二个源文件
Source_002.csv。此执行将向已填充的目标表附加行。在包执行后，请查询目标表
CategoryName以查找以下添加数据，并注意Id列已正确填充。
希望这有所帮助。
存储过程脚本:
CREATE PROCEDURE [dbo].[PopulateDestination]
AS
BEGIN
SET NOCOUNT ON;
INSERT INTO dbo.CategoryName (Id, CatType, Name, LegacyId)
SELECT MAXID.Id + RANK() OVER(PARTITION BY CatType ORDER BY LegacyId) Id
, CS.CatType
, CS.Name
, CS.LegacyId
FROM dbo.CategoryName_Staging CS
CROSS APPLY (
SELECT COALESCE(MAX(Id), 0) Id
FROM dbo.CategoryName C
WHERE C.CatType = CS.CatType
) MAXID
END
