在SSIS中从Excel Plus源中检索重复的键行 [英] Retrieve Duplicate key rows from Excel Plus Source in SSIS

问题描述

我是SSIS的新手.我的SSIS软件包包含:

I am new to SSIS. My SSIS package contains:

1.Extract Data from Excel file (Excel Source Plus Component)
2.Added new Columns (Derived column Transformation)
3.Store Data into SQL Server (OLE DB Destination)

我的要求，如果excel文件中存在相同ID和用户名的多个行，则此SSIS包应检索包含重复行的行，并将整行存储到另一个表中，并在派生列之前停止包执行转型.
如果key column不存在重复值，则仅应继续执行.我的表结构如下:

My Requirement if more than one row exists for same ID and username in the excel file, this SSIS package should retrieve the rows which contains duplicate rows and store the entire row into the another table and stop the package execution before the derived column transformation.
If there is no duplicate values exists for key column , then only the execution should proceed. My table structure is like below:

ID username Name Address EmailID PhoneNo
----------------------------------------
1  ABC      MNO   ASDA   asd     345
1  ABC      XYZ   ASD    DF      678
2  SDF      MNO   sdF    sdf     2323

上表应在派生列转换之前停止，并将2行(ID:1,2)存储在另一个表中.

The above table should be stopped before the derived column transformation and store the 2 rows (ID:1,2) in another table.

1. 我尝试了汇总转换，但是我不想将group by子句应用于其余的非关键列.如果我不应用其余列中没有任何列，它们不会出现在输出中.
2. 我尝试了查找"列和多联接，但我不了解自己加入.

帮助我解决此问题.谢谢.

Help me to resolve this problem.Thank You.

推荐答案

将所有行导入到新的登台表中.然后发出一条SQL命令，该命令调用一个存储过程，该存储过程将行定向到适当的目标表，然后截断暂存表以进行下一次运行.

Import all the rows into a new staging table. Then issue a SQL command that calls a stored procedure that directs the rows to the appropriate destination tables and then truncates the staging table for the next run.

这比在数据流过程中可以做的任何事情都快得多.

This will work MUCH faster than anything you can do during the dataflow process.

这篇关于在SSIS中从Excel Plus源中检索重复的键行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！