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，而是删除要沿管道传输的数据并输出 DELETED 虚拟表.有点像

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屋！