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

问题描述

我有一个SSIS例程，它从一个非常动态的表中读取数据，并将它发现的任何行插入到另一个数据库的表中，然后截断原始的源表。 >由于源表的动态性质，这个截断不会令人惊讶地导致行不会到第二个数据库。

只删除那些行的最好方法是什么已经被迁移了？

源表格上有一个标识列，但不会被迁移。

我无法更改任何表模式。

解决方案

一个选项可能听起来很愚蠢，并使用 OUTPUT 子句。

我创建了一个简单的控制流，为我填充一个表。

  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 
选择
 V.name 
起价
 master.dbo.spt_values V 
，其中
 V.name不是NULL; 
  

 删除
 DF 
 OUTPUT 
 DELETED。* 
 FROM 
 dbo.DeleteFirst AS DF; 
  

它的工作原理是这样的！

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.

解决方案

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;

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;

It works, it works!

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