sql-server - 需要基于SSIS中的其他列来增加列值

Question

我有一个类似下面的情况。

源数据如下(XML文件)::

ID        CatType           Name
11           A              Raj
22           A              Rai
33           B              Raki
44           B              Krish
55           A              Rem
66           B              Ram

我必须把上面装进下面的甲壳虫。

ID       CatType        Name       LegacyID
1              A        Raj           11
2              A        Rai           22
1              B        Raki          33
2              B        Krish         44
3              A        Rem           55
3              B        Ram           66

ID和 CatType是我的目标表中的组合键。我从源头获取CatType。在加载数据时，我必须通过在目标表中选择Max(ID)(其中CatType =？(基于CatType))来增加ID。
我如何将这些记录加载到SSIS中，有人可以向我指出正确的方向吗？

Answer 1

这是一个使用SSIS 2008 R2创建的示例SSIS包，用于演示您要实现的目标。该示例包将传入的文件数据加载到临时表中。然后，将SQL Server Rank函数与Partition子句结合使用，可以根据需要加载数据。本示例假定您的数据库版本为SQL Server 2005 or above。由于您未提供确切的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。

双击第一个“执行SQL”任务并将其配置为截断登台表。

双击“数据流任务”以切换到“数据流”选项卡。在“数据流”选项卡中，放置一个平面文件源管理器以读取CSV文件，并放置一个OLE DB目标以将数据写入登台表。

如下所示配置平面文件源，以使用平面文件连接管理器读取平面文件源。

如下所示配置OLE DB设计，以接受传入的数据并写入目标文件。

返回“控制流”选项卡，配置最后一个Execute SQL任务以调用新创建的存储过程。程序包开发现已完成。

执行程序包。请记住，该程序包配置为仅读取第一个源文件 Source_001.csv 。程序包执行后，查询目标表 CategoryName 以查找以下数据。

现在，停止SSIS包的执行，双击名为的平面文件连接管理器Source 。将文件名路径更改为 Source_002.csv 以便读取第二个文件。

再次执行该程序包。现在，该软件包已配置为读取第二个源文件 Source_002.csv 。此执行会将行追加到已填充的目标表中。程序包执行后，查询目标表 CategoryName 以查找以下附加数据，并注意Id列正确填充。

希望能有所帮助。

Stored Procedure Script :

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