SSIS Foreach 通过一个表，插入另一个表并删除源行 [英] SSIS Foreach through a table, insert into another and delete the source row

问题描述

我有一个 SSIS 例程，它从一个非常动态的表中读取数据，然后在截断原始源表之前将它找到的任何行插入到不同数据库中的表中.

I have an SSIS routine that reads from a very dynamic table and inserts whichever rows it finds into a table in a different database, before truncating the original source table.

由于源表的动态特性，这种截断不会令人惊讶地导致行无法进入第二个数据库.

Due to the dynamic nature of the source table this truncation not surprisingly leads to rows not making it to the second database.

仅删除那些已迁移的行的最佳方法是什么?

What is the best way of deleting only those rows that have been migrated?

源表上有一个标识列，但没有迁移过来.

There is an identity column on the source table but it is not migrated across.

我无法更改任一表架构.

I can't change either table schema.

推荐答案

一个选项，这听起来可能很愚蠢但确实有效，是先删除并使用 OUTPUT 子句.

A option, that might sound stupid but it works, is to delete first and use the OUTPUT clause.

我创建了一个简单的控制流来为我填充表格.

I created a simple control flow that populates a table for me.

IF EXISTS
(
    SELECT 1 FROM sys.tables AS T WHERE T.name = 'DeleteFirst'
)
BEGIN
    DROP TABLE dbo.DeleteFirst;
END

CREATE TABLE dbo.DeleteFirst
(
    [name] sysname
);

INSERT INTO
    dbo.DeleteFirst
SELECT
    V.name
FROM
    master.dbo.spt_values V
WHERE
    V.name IS NOT NULL;

在我的 OLE DB 源中，不使用 SELECT，而是删除您想要进入管道的数据并输出已删除的虚拟表.类似的东西

In my OLE DB Source, instead of using a SELECT, DELETE the data you want to go down the pipeline and OUTPUT the DELETED virtual table. Somethinng like

DELETE
    DF
OUTPUT
    DELETED.*
FROM
    dbo.DeleteFirst AS DF;

有效，有效！

这篇关于SSIS Foreach 通过一个表，插入另一个表并删除源行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！