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
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.
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.
