SSIS OLEDB命令转换(如果不存在，请插入) [英] SSIS OLEDB Command transformation (Insert if not exists)

问题描述

根据 Microsoft文档的OLE DB命令转换， SSIS会这样做

Ok so according to Microsoft docs the OLE DB Command Transformation in SSIS does this

OLE DB Command转换为数据流中的每一行运行一个SQL语句.例如，您可以运行一条SQL语句来插入，更新或删除数据库表中的行.

The OLE DB Command transformation runs an SQL statement for each row in a data flow. For example, you can run an SQL statement that inserts, updates, or deletes rows in a database table.

所以我想写一些SQL来在我的一个表中插入行仅当记录不存在时

So I want to write some SQL to Insert rows in one of my tables only IF the record doesn't exists

所以我尝试了这个，但是控件不断抱怨不良的sintaxys

So I tried this but the controls keeps complaining of bad sintaxys

IF NOT EXISTS
(SELECT * FROM M_Employee_Login WHERE 
           Column1=?
           AND Column2=?
           AND Column3=?)

INSERT INTO [M_Employee_Login]
           ([Column1]
           ,[Column2]
           ,[Column3])
     VALUES
           (?,?,?)

但是，如果我删除了IF NOT EXISTS部分(仅保留插入内容)，则控件说可能代码没问题，那我在做什么错了.

However if I remove the IF NOT EXISTS section (leaving the insert only) the controls says may code is Ok, what am I doing wrong.

有更简单的解决方案吗?

Is there an easier solution?

更新:顺便说一句，我的来源是平面文件(csv文件)

Update: BTW My source is a Flat File (csv file)

自回答以来更新:只是让人们知道.我最终使用了OLE DB Command Transformation，就像我计划的原因要比该操作中的OLE DB Destination更好.区别在于我确实使用了Lookup Component来过滤所有已经存在的记录(如建议的答案).然后将OLE DB Command Transformation与我在问题中使用的Insert SQL结合使用，它可以按预期工作.希望对您有帮助

Update since answer: Just to let people know. I ended up using the OLE DB Command Transformation like I planned cause is better than the OLE DB Destination for this operation. The difference is that I did used the Lookup Component to filter all the already existent records (like the answer suggested). Then use the OLE DB Command Transformation with the Insert SQL that I had in the question and it worked as expected. Hope it helps

推荐答案

OLEDB Command对象与OLE DB Destination

而不是按照您的描述进行操作，而是使用Lookup Component.您的数据流将变为平面文件源->查找组件-> OLE DB目标

Rather than doing it as you describe, instead use a Lookup Component. Your data flow becomes Flat File Source -> Lookup Component -> OLE DB Destination

在您的查找中，您将编写查询SELECT Column1, Column2, Column3 FROM M_Employee_Login并对其进行配置，以使其不将匹配的实体重定向到流而不是失败(取决于您的版本2005，而不是2005)，这将是默认值.

In your lookup, you will write the query SELECT Column1, Column2, Column3 FROM M_Employee_Login and configure it such that it will redirect no match entities to the stream instead of failure (depending on your version 2005 vs not 2005) this will be the default.

查找之后，不匹配"的输出将包含在目标表中找不到对应匹配的值.

After the lookup, the output of No Match will contain the values that didn't find a corresponding match in the target table.

最后，配置OLEDB目标以执行快速加载选项.

Finally, configure your OLEDB Destination to perform the fast load option.

这篇关于SSIS OLEDB命令转换(如果不存在，请插入)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！