Obtendo dados novos e atualizados com uma coluna de arquivo

Como você está usando o SSIS e capturando as linhas alteradas, você deve fazer com que o SSIS capture os valores PK para as linhas que estão sendo processadas. A última etapa do pacote deve ser fazer uma atualização direcionada na tabela de origem, definindo o [Archive]campo como 1 com base nesses valores de PK.

E se ainda não tiver feito, o acionador de atualização precisa excluir essas alterações da configuração do [Archive]campo como 2. Você provavelmente precisará de algo como:

UPDATE tab
SET    tab.[Archive] = 2
FROM   dbo.MyTable tab
INNER JOIN INSERTED ...
INNER JOIN DELETED ...
WHERE INSERTED.Archive = DELETED.Archive
AND   DELETED.Archive IN (0, 1) -- Add 3 to this list; see below for why

MAS, então você precisa lidar com o cenário em que uma linha é atualizada durante o processo ETL. Você ainda deseja ETL que muda na próxima vez. Mas se o processo ETL estiver em execução, ele mudará o [Archive]campo para 1 no final e não haverá registro dessa linha sendo alterada. Então você precisa de outro valor para indicar "Being ETLed", talvez 3. Você pode então, semelhante ao que @billinkc sugeriu em um comentário sobre a questão, faça um UPDATEcom a OUTPUTcláusula da seguinte forma:

UPDATE TOP (@BatchSize) tab
SET    tab.[Archive] = 3
OUTPUT INSERTED.*
FROM   dbo.MyTable tab
WHERE  tab.[Archive] IN (0, 2)
ORDER BY tab.[Archive] ASC;

Então, na etapa final do pacote SSIS, a atualização de destino deve estar em ambos os valores PK AND [Archive] = 3. E adicione 3à INlista na consulta de atualização do acionador acima. Dessa forma, apenas os registros que não foram alterados durante o processo ETL serão atualizados para 1, enquanto os registros que foram alterados durante o ETL aparecerão como 2e serão selecionados novamente na próxima vez com seus valores modificados.

Claro, você também pode querer criar um índice filtrado no [Archive]campo para suportar a obtenção de registros marcados como 0, 2 ou 3. Portanto:

CREATE INDEX ... WHERE [Archive] IN (0, 2, 3);

Você deseja obter os registros marcados como 3porque, supondo que seja um thread único por servidor de campo, os registros marcados como 3representam registros travados devido a um processo anterior que falhou. É também por isso que você não deseja marcar os registros como quando 1os UPDATE...OUTPUTobteve inicialmente; se o processo falhar, eles serão marcados como já arquivados e não serão recuperados.

Dito isso, talvez seja melhor usar uma tabela de filas em vez de um campo de status. Você pode criar uma tabela simples com apenas alguns campos:

QueueID BIGINT NOT NULL IDENTITY(1, 1) PRIMARY KEY,
MyTablePKfield  INT NOT NULL,
CreateDate DATETIME NOT NULL DEFAULT (GETDATE()) -- or GETUTCDATE()

Em seguida, o gatilho se torna um INSERT, UPDATEgatilho simples que apenas faz:

INSERT INTO dbo.MyTableEtlQueue (PKfield)
  SELECT ins.PKfield
  FROM   INSERTED ins;

Em seguida, o processo SSIS obtém os valores por meio de:

SELECT TOP (@BatchSize) etl.QueueID, tab.field1, tab.field2...
FROM   dbo.MyTableEtlQueue etl
INNER JOIN dbo.MyTable tab
        ON tab.PKfield = etl.MyTablePKfield
ORDER BY etl.QueueID ASC;

E o final do processo SSIS faz uma exclusão simples na [MyTableEtlQueue]tabela com base nos [QueueID]valores extraídos no início.

Nesse modelo, você não precisa do índice filtrado ou da coluna de status em uma tabela grande, o que reduz a contenção e o tamanho dessa tabela já grande. Você não precisa se preocupar com registros em trânsito, seja em termos de atualizações ou falhas de processo.

EDIT:
Dada a nova informação da tabela de origem que não possui exclusividade, isso requer apenas uma pequena alteração neste método da tabela de filas: em vez de rastrear apenas o PKfield (que aparentemente não existe ;-), rastreie todos os campos ( ou pelo menos tudo o que precisa ser ETLed).

Portanto, a tabela de filas muda para:

QueueID BIGINT NOT NULL IDENTITY(1, 1) PRIMARY KEY,
MyTableField1  INT NOT NULL,
MyTableField2  INT NOT NULL,
...
MyTableFieldN  INT NOT NULL,
CreateDate DATETIME NOT NULL DEFAULT (GETDATE()) -- or GETUTCDATE()

E o gatilho muda para:

INSERT INTO dbo.MyTableEtlQueue (PKfield)
  SELECT ins.MyTableField1, ins.MyTableField2, ..., ins.MyTableField1N
  FROM   INSERTED ins;

Em seguida, o processo SSIS muda para ser (observe a falta de JOIN na tabela de origem agora):

SELECT TOP (@BatchSize) etl.QueueID, etl.MyTableField1, etl.MyTableField2, ...
FROM   dbo.MyTableEtlQueue etl
ORDER BY etl.QueueID ASC;

Esse método de tabela de fila isola a tabela de origem (e seu processamento) do processo de auditoria/ETL (muito parecido com o que o CDC faz). Isso permite que seu aplicativo interaja com a tabela de origem sem problemas, e nenhuma nova coluna que ocupe mais espaço na página de dados, e nenhuma transação potencialmente longa segurando a tabela de origem enquanto o ETL processa.

Que tal fazer uso da mesa virtual OUTPUT ? Defina corretamente o nível de isolamento da transação (instantâneo/serializável) para que você só veja as linhas a partir do momento em que seu processo é iniciado.

Use o seguinte para sua fonte OLE DB

UPDATE T SET Archive = 1 OUTPUT DELETED.* FROM Table T WHERE T.Archive = 0;

Isso atualiza tudo em uma boa operação atômica com um efeito colateral de gerar a saída de destino em seus buffers de fluxo de dados. Encaminhe isso para o seu destino e pronto. Agradável e arrumado

O Change Tracking (CT) e o Change Data Capture (CDC) são integrados ao SQL Server. O CT pode identificar as linhas que mudaram desde a última vez que você perguntou. O CDC pode fornecer um histórico completo de todas as alterações de dados desde a última vez que você perguntou. Ambos são facilmente manipulados a partir do SSIS.

Não haverá necessidade de redefinir sinalizadores por linha após o processamento. O sistema lida com tudo isso por meio de tabelas ocultas adicionais especificamente para essa finalidade.

Uma solução SSIS pura seria entregar os Fluxos de Dados para as tarefas Inserir e Atualizar nas Transformações de Cache.

http://msdn.microsoft.com/en-us/library/bb895264.aspx

Se eles ocorrerem em 1 fluxo de dados, você poderá enviá-los para um único cache, caso contrário, você precisará de 2.

Em seguida, você pode testar esses Caches em Fluxos de Dados posteriores usando a transformação de Pesquisa. Eu redirecionaria todas as linhas correspondentes para descartá-las efetivamente e, em seguida, continuaria apenas com as linhas não correspondentes.

Nota Os caches são armazenados na memória, então tente manter o conteúdo enxuto, por exemplo, apenas a(s) coluna(s) chave. Contanto que você não esgote a memória física, o desempenho do tempo de execução será muito rápido (sem E/S).

Essa solução é provavelmente a mais simples de construir e testar, pois não precisa de código para ser implementada.