SSIS-删除行 [英] SSIS - Delete rows

问题描述

我是SSIS的新手，在这方面需要帮助。我发现了文章，其中介绍了如何检测存在的和已更改的行。我缺少的部分是如何更新已更改的行。我发现有些文章说删除删除已更改的记录并插入新的记录集也是很好的解决方案。问题是我不知道如何删除（红色框）。

I'm new to SSIS and need help on this one. I found an article which describes how to detect rows which exist and which have changed. The part that I'm missing is how to update rows that changed. I found some articles which say that it's also good solution to delete records which have changed and insert new recordset. The thing is I don't know how to do that step of deleting (red box).

有任何建议吗？

推荐答案

如果必须删除数据流任务中的行，则需要使用 OLE DB命令转换并写一个DELETE语句，例如 DELETE FROM dbo.Table WHERE ColumnName =？。然后，在OLE DB Command转换的列映射中，您将把由问号表示的参数与来自先前转换的数据进行映射。在您的情况下，数据来自Union All 2。

If you have to delete the rows within Data Flow Task, then you need to use the OLE DB Command transformation and write a DELETE statement like DELETE FROM dbo.Table WHERE ColumnName = ?. Then in the column mappings of the OLE DB Command transformation, you will map the parameter represented by the question mark with the data that comes from the previous transformation. In your case, the data that comes from Union All 2.

但是，我不建议您使用该选项，因为 OLE DB Command 执行

However, I wouldn't recommend that option because OLE DB Command executes for every row and it might slow down your package if there are too many rows.

我建议这样：

1. 将输出从 Union All 2 重定向到临时登台表（例如 dbo.Staging >）使用 OLE DB目标。

1. Redirect the output from the Union All 2 to a temporary staging table (say dbo.Staging) using OLE DB Destination.

让我们假设您的最终目标表为 dbo.Destination 。现在，您的登台表具有应从表Destination中删除的所有记录。

Let's us assume that your final destination table is dbo.Destination. Now, your Staging table has all the records that should be deleted from the table Destination.

在 Control Flow 选项卡上，放置数据流任务之后的执行SQL任务。在执行SQL任务中，编写SQL语句或使用存储过程（该过程将调用SQL语句来联接暂存和Destination之间的记录）以从Destination表中删除所有匹配的行。

On the Control Flow tab, place an Execute SQL Task after the Data Flow Task. In the Execute SQL Task, write an SQL statement or use a stored procedure that would call an SQL statement to join the records between Staging and Destination to delete all the matching rows from Destination table.

此外，在数据流任务之前放置另一个Execute SQL任务。在此执行SQL任务中，从登台表中删除/截断行。

Also, place another Execute SQL Task before the Data Flow Task. In this Execute SQL Task, delete/truncate rows from the Staging table.

类似的方法可能会删除

DELETE      D
FROM        dbo.Destination D
INNER JOIN  dbo.Staging     S
ON          D.DestinationId = S.StagingId

希望有帮助。

这篇关于SSIS-删除行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！