SQL Server 2008 SSIS – Change Data Capture (CDC) Parte 2

CDC e Integration Service (SSIS)
No ultimo post escrevi sobre uma nova funcionalidade do SQL Server 2008 chamada Change Data Capture (CDC), onde é criada uma infra-instrutora para registrar as alterações que ocorrem em uma tabela, ficando simples identificar as alterações nos dados durante a carga com o Integration Service (SSIS).

Para maiores detalhes veja a primeira parte em http://sqlserver-brasil.blogspot.com/2008/11/sql-server-2008-ssis-change-data.html.

Neste post vamos ver como utilizar o CDC dentro do SSIS, identificando facilmente as alterações nos dados durante a carga.

No post anterior mostrei o conteúdo da tabela cdc.dbo_Cliente_CT que contém o registro das operações que ocorreram na tabela Cliente, veja na imagem abaixo:

Select * from cdc.dbo_Cliente_CT

As linhas identificadas no resultado acima representam:
Azul INSERT (_$operation = 2)
Vermelho UPDATE antes da alteração (_$operation = 3)
Vermelho UPDATE após a alteração (_$operation = 4)
Verde DELETE (_$operation = 1)

O primeiro problema que temos é identificar as alterações que ocorreram em um determinado período (entre duas cargas de dados), porque na tabela acima não existe uma coluna com data e hora! A chave para resolver este problema está na coluna _$start_lsn, que contém uma numeração em Hexa seqüencial (LSN – Log Sequence Number). A função fn_cdc_map_time_to_lsn converte uma data e hora no LSN mais próximo! O script abaixo identifica o intervalo de LSN a ser utilizado na consulta na tabela acima:

DECLARE @Data_INI datetime, @Data_FIM datetime,
@lsn_INI binary(10), @lsn_FIM binary(10);

SET @Data_INI = GETDATE() -1
SET @Data_FIM = GETDATE();

SET @lsn_INI = sys.fn_cdc_map_time_to_lsn
('smallest greater than or equal', @Data_INI);

SET @lsn_FIM = sys.fn_cdc_map_time_to_lsn
('largest less than or equal', @Data_FIM);

O próximo problema é identificar as operações que devem ser realizadas durante a carga, utilizando as entradas na tabela cdc.dbo_Cliente_CT, dentro do período especificado acima. No exemplo da figura acima temos seis linhas, onde as três primeiras são inclusões, a quarta e quinta uma atualização e a última exclusão.

1) A cliente Maria foi incluída e depois não sofreu alteração, por isso esta deverá ser inserida no destino.

2) O cliente Jose foi incluído e depois alterado, sendo assim este cliente deverá ser inserido no destino com o telefone 2211-1111, que foi modificado na alteração registrada nas linhas quatro e cinco.

3) O cliente Antonio foi incluído e depois excluído (linhas 3 e 6 respectivamente), por isso este não deve fazer parte da carga.

Para facilitar o SQL Server 2008 disponibiliza a função cdc.fn_cdc_get_net_changes_dbo_cliente (em azul a porção variável do nome) que realiza a análise acima em um intervalo de LSN e retorna apenas as operações que devem ser feitas no destino. No exemplo acima o retorno da função seria: inclusão da Maria e Jose com o telefone novo! Na Stored Procedure abaixo temos o procedimento completo, ficando fácil utilizar este recurso no Integration Service:

CREATE PROC dbo.cdc_Cliente
@Data_INI datetime, @Data_FIM datetime
as
SET @Data_INI = GETDATE() -1
SET @Data_FIM = GETDATE();
DECLARE @lsn_INI binary(10), @lsn_FIM binary(10);

SET @lsn_INI = sys.fn_cdc_map_time_to_lsn
('smallest greater than or equal', @Data_INI);

SET @lsn_FIM = sys.fn_cdc_map_time_to_lsn
('largest less than or equal', @Data_FIM);

SELECT *
FROM cdc.fn_cdc_get_net_changes_dbo_cliente
(@lsn_INI, @lsn_FIM, 'all');
go

No próximo post veremos um exemplo de pacote SSIS utilizando os recursos deste post, até lá.
Landry.