ssis - 使用SSIS包导入大型数据集时，如何执行查找？

Question

我有一个问题-我希望通过SSIS（Microsoft ETL）下载客户数据。对于我导入的每个客户记录，我都必须对照目标数据库中的现有客户记录进行检查。如果记录存在，则必须更新记录，如果不存在，则必须插入它。最佳表现的策略是什么？我们正在谈论20-30百万行。

我想到了触发器（在INSERT上触发-检查记录是否存在），查找，游标，但是我确定有更好的方法，可能使用SSIS中的一种数据流转换？

还是将其导入到临时表中然后在本地进行查找会更好？

Answer 1

这是一个可能的选项，无论是否涉及插入/更新，您都可以将其加载到目标表中。下面给出的示例在3分钟内将包含一百万行的文本文件加载到SQL表中。如果该记录在SQL表中不存在，那么它将被插入到目标表中，否则该记录将被插入到登台表中，该表将在存储过程的帮助下用于更新目标表。该存储过程还将删除SQL表中可能存在但平面文件中不存在的所有记录。该示例将SSIS 2008 R2与SQL Server 2008 R2后端一起使用。

分步过程：


在SQL Server数据库中，创建两个表，即dbo.ItemInfo和dbo.Staging。 “脚本”部分下提供了创建表查询。这些表的结构显示在屏幕快照＃1中。 ItemInfo将保存实际数据，而Staging表将保存登台数据以比较和更新实际记录。这两个表中的Id列都是自动生成的唯一身份列。表ItemInfo中的IsProcessed列将用于标识和删除不再有效的记录。
创建一个SSIS包并创建5个变量，如屏幕快照2所示。我已经对制表符分隔的文件使用了.txt扩展名，因此在变量FileExtension中使用了值*.txt。 FilePath变量将在运行时分配值。 FolderLocation变量表示文件将位于何处。 SQLPostLoad和SQLPreLoad变量表示在预加载和后加载操作期间使用的存储过程。这些存储过程的脚本在“脚本”部分下提供。
创建一个指向SQL Server数据库的OLE DB连接。创建一个平面文件连接，如屏幕快照＃3和＃4所示。平面文件连接列部分包含列级别的信息。屏幕截图5显示了列数据预览。
如屏幕截图6所示，配置控制流任务。如屏幕快照＃7-＃10所示，配置任务Pre Load，Post Load和Loop Files。 Pre Load将截断登台表，并将ItemInfo表中的所有行的IsProcessed标志设置为false。加载后将更新更改，并将删除文件中未找到的数据库中的行。请参阅这些任务中使用的存储过程，以了解这些Execute SQL任务正在执行的操作。
双击“加载项目”数据流任务，然后按截图11所示进行配置。 Read File是配置为使用平面文件连接的平面文件源。 Row Count是派生的列转换，其配置显示在screenshto＃12中。 Check Exist是一个查找转换，其配置显示在屏幕快照＃13-＃15中。查找无匹配输出被重定向到左侧的Destination Split。查找匹配输出将重定向到左侧的Staging Split。 Destination Split和Staging Split具有与屏幕截图＃16中所示完全相同的配置。目标表和登台表中有9个不同目标的原因是为了提高程序包的性能。
所有目标任务0-8已配置为将数据插入表dbo.ItemInfo中，如屏幕快照＃17所示。所有暂存任务0-8已配置为将数据插入dbo.Staging，如屏幕快照＃18所示。
在平面文件连接管理器上，将ConnectionString属性设置为使用变量FilePath，如屏幕快照＃19所示。这将使程序包在循环访问文件夹中的每个文件时使用变量中设置的值。


测试场景：

Test results may vary from machine to machine. 
In this scenario, file was located locally on the machine. 
Files on network might perform slower. 
This is provided just to give you an idea. 
So, please take these results with grain of salt.

程序包在具有至强单核CPU 2.5GHz和3.00 GB RAM的64位计算机上执行。
用 1 million rows加载了平面文件。包执行大约2分钟47秒。请参阅屏幕截图＃20和＃21。
使用“测试查询”部分下提供的查询来修改数据，以在程序包的第二次运行期间模拟更新，删除和创建新记录。
在数据库中执行以下查询后，装入包含 1 million rows的相同文件。包执行大约1分钟35秒。请参阅屏幕截图22和23。请注意在屏幕快照＃22中重定向到目标和登台表的行数。


上面的答案也是 this Stack Overflow问题的答案的副本。

希望有帮助。

测试查询：
。

--These records will be deleted during next run 
--because item ids won't match with file data.
--(111111 row(s) affected)
UPDATE dbo.ItemInfo SET ItemId = 'DEL_' + ItemId WHERE Id % 9 IN (3)

--These records will be modified to their original item type of 'General'
--because that is the data present in the file.
--(222222 row(s) affected)
UPDATE dbo.ItemInfo SET ItemType = 'Testing' + ItemId WHERE Id % 9 IN (2,6)

--These records will be reloaded into the table from the file.
--(111111 row(s) affected)
DELETE FROM dbo.ItemInfo WHERE Id % 9 IN (5,9)

平面文件连接列
。

Name        InputColumnWidth     DataType          OutputColumnWidth
----------  ----------------     ---------------   -----------------
Id          8                    string [DT_STR]   8
ItemId      11                   string [DT_STR]   11
ItemName    21                   string [DT_STR]   21
ItemType    9                    string [DT_STR]   9

脚本：（用于创建表和存储过程）
。

CREATE TABLE [dbo].[ItemInfo](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [ItemId] [varchar](255) NOT NULL,
    [ItemName] [varchar](255) NOT NULL,
    [ItemType] [varchar](255) NOT NULL,
    [IsProcessed] [bit] NULL,
    CONSTRAINT [PK_ItemInfo] PRIMARY KEY CLUSTERED ([Id] ASC),
    CONSTRAINT [UK_ItemInfo_ItemId] UNIQUE NONCLUSTERED ([ItemId] ASC)) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Staging](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [ItemId] [varchar](255) NOT NULL,
    [ItemName] [varchar](255) NOT NULL,
    [ItemType] [varchar](255) NOT NULL,
 CONSTRAINT [PK_Staging] PRIMARY KEY CLUSTERED ([Id] ASC)) ON [PRIMARY]
GO

CREATE PROCEDURE [dbo].[PostLoad]
AS
BEGIN
    SET NOCOUNT ON;

    UPDATE      ITM
    SET         ITM.ItemName    = STG.ItemName
            ,   ITM.ItemType    = STG.ItemType 
            ,   ITM.IsProcessed = 1
    FROM        dbo.ItemInfo    ITM
    INNER JOIN  dbo.Staging     STG
    ON          ITM.ItemId      = STG.ItemId;

    DELETE FROM dbo.ItemInfo
    WHERE       IsProcessed = 0;
END
GO

CREATE PROCEDURE [dbo].[PreLoad]
AS
BEGIN
    SET NOCOUNT ON;

    TRUNCATE TABLE dbo.Staging;     

    UPDATE  dbo.ItemInfo 
    SET     IsProcessed = 0;
END
GO

屏幕截图1：



屏幕截图2：



屏幕截图3：



屏幕截图4：



屏幕截图5：



屏幕截图6：



屏幕截图7：



截图8：



屏幕截图9：



屏幕截图＃10：



屏幕截图＃11：



屏幕截图＃12：



截图13：



屏幕截图＃14：



屏幕截图15：



屏幕截图＃16：



屏幕截图17：



屏幕截图＃18：



屏幕截图＃19：



屏幕截图＃20：



屏幕截图＃21：



屏幕截图＃22：



屏幕截图＃23：