使用SSIS从平面文件源中删除重复的值 [英] Remove the duplicate values from the flat file source using SSIS

问题描述

大家好，

谁能指导我.我必须从使用SSIS导入SQL Server的平面文件源中删除重复的行.

现在的问题是，在开始无痛加载之前，我必须从输入源(即平面文件)中删除重复的行.

谷歌搜索后发现添加带有删除重复选项的排序将可以工作，但是会大大降低执行速度.很少有链接说转换或派生列，但我担心它不会工作.而且我必须保持执行时间短，因为我有数百万条记录.

任何方向都很好.

Hi All,

Can any one please direct me. I have to remove the duplicate rows from the flat file source which is imported into SQL server using SSIS.

Now the problem is i have to remove the duplicate rows if any from the input source i.e flat file before starting the increamental load.

googled and found adding sort with remove duplicate options will work but it drastically slows the execution.And few links say transformation or derived columns but i fear it wont work. And i have to keep execution time low as i have millions of records.

Any directions would be great. thanks.

推荐答案

您可以做的是在导入后将数据复制到另一个具有标识符字段的表中. 在下一条语句中，您可以确定双打，并通过唯一的ID知道可以删除双打
之后，您可以将它们设置到需要数据的表中

What you can do is after import copy the data to another table that has an identifier field.
With the next statement you can determine the doubles and know by the unique ID that you can delete the doubles
after that you can set them into the table that needs the data

select uniqueID, EmployeeID FROM
(
    select Row_Number() over(partition by EmployeeID order by EmployeeID) as Rowno, uniqueID, EmployeeID from MyTableWithUniqueIDAdded
) as t
where t.Rowno > 1

足够清楚吗?

clear enough?

感谢您的答复digimanus.我终于使用了 http://social.msdn.microsoft .com/Forums/zh-CN/sqlintegrationservices/thread/0d730d7c-0f15-4a97-9701-e66590fc2aa4 [

Thanks for reply digimanus. I finally used http://social.msdn.microsoft.com/Forums/en-US/sqlintegrationservices/thread/0d730d7c-0f15-4a97-9701-e66590fc2aa4[^]
As the performance being a key i just used staging table.Used SSIS to remove the duplicate records from the staging table by using execute sql task.

这篇关于使用SSIS从平面文件源中删除重复的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！