Com a chegada do SQL Server 2012 melhorias foram realizadas e novidades implementadas e é uma dessas novidades que apresentarei.

No SSIS alguns novos componentes foram implementados para facilitar a utilização do CDC (Change Data Capture):

clip_image001 Esse componente se encontra no Control Flow e ele que irá realizar todo o controle necessário acessar as informações através do Log Sequence Number (LSN) . Mais informação sobre o CDC Contral Task acesse o link.

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

clip_image002 Esse componente se encontra dentro do Data Flow e ele irá realizar a leitura dos seus dados. Mais informação sobre o CDC Source acesse o link.

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

clip_image003 Esse componente se encontra dentro do Data Flow e ele irá separar seus dados de acordo com as opções escolhidas dentro do CDC Source. Mais informações sobre o CDC Splitter acesso o link.

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

Preparação do Ambiente

O CDC tem inúmeras utilidades, mas uma delas é ajudar no processo de carga de um ambiente de Data Warehouse e esse foi o cenário que escolhi.

1º Passo – Criação dos databases

Irei utilizar 3 databases simulando um ambiente de carregamento de dados em um DW.

 1: CREATE DATABASE EXEMPLO_CDC

 2: GO

 3: CREATE DATABASE STAGING

 4: GO

 5: CREATE DATABASE DW_EXEMPLO_DCD

 6: GO

2º Passo – Preparando os databases

O database EXEMPLO_CDC ira servir como databases OLTP, onde todos os dados transacionais serão mantidos e é nele que temos que habilitar o CDC.

 1: USE EXEMPLO_CDC

 2: GO

 3: EXECUTE sys.sp_cdc_enable_db

 4: GO

Para mais informações sobre a stored procedure de sistema acesse o link http://msdn.microsoft.com/en-us/library/bb510486

Agora crie uma tabela e habilite o CDC para esse objeto.

 1: CREATE TABLE [dbo].[CATEGORIA]

 2: (ID INT NOT NULL IDENTITY PRIMARY KEY,

 3: NOME_CATEGORIA VARCHAR(100) NOT NULL)

 4:

 5: EXECUTE sys.sp_cdc_enable_table

 6: @source_schema = N'dbo',

 7: @source_name = N'CATEGORIA',

 8: @role_name = N'cdc_admin',

 9: @supports_net_changes = 1

 10: GO

 11:

Para mais informaçõe sobre a stored procedure de sistema acesso o link

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

 1: INSERT INTO [dbo].[CATEGORIA] VALUES

 2:  ('CATEGORIA 1')

 3: ,('CATEGORIA 2')

 4: ,('CATEGORIA 3')

 5: GO

 6:

 7: USE DW_EXEMPLO_CDC

 8: GO

 9: CREATE TABLE [dbo].[CATEGORIA_DESTINO]

 10: (ID INT NOT NULL PRIMARY KEY,

 11: NOME_CATEGORIA VARCHAR(100) NOT NULL)

 12:

Criando Pacote

Para esse exemplo irei criar dois pacotes, o primeiro de carga inicial ou carga completa e o segundo de carga incremental.

Pacote Inicial

Nesse pacote iremos criar uma carga completa dos dados. A primeira tarefa a se realizar é utilizar o componente CDC Control Task.

Algumas configurações importantes devem ser feitas nesse componentes.

  • CDC control operation: Utilize a opção Mark Initial load start. Esta operação é usada no começo de um pacote da carga inicial para registrar o LSN atual no banco de dados de origem antes de o pacote da carga inicial começar a ler as tabelas de origem.
  • Crie uma variável, User::CDC_State, para armazenar o estado da operação do CDC. O botão New faz isso automaticamente.
  • Utilize a opção Automatically store in a database table para persistir o estado do CDC.
  • Crie uma nova tabela para armazenar o estado do CDC. Clique em New e o assistente irá te fornecer um código para ser executado. Esta tabela será usada para rastrear as informações de carga CDC, de modo que você só pegar as novas alterações a cada vez que o pacote de carga incremental é executado
  • State Name: O nome associado ao estado persistente. Esse nome é usado para verificar a linha de estado na tabela de estado.

clip_image004

Figura 1

Proxima tarefa é iniciar seu Data Flow que nesse exemplo é bem simples.

clip_image005

Figura 2

Despois de realizar todos as manipulações de dados temos que novamente colocar o componente de CDC Control Task porem com uma modificação. Altere a opção CDC control operation para Mark initial load end

clip_image006

Figura 3

Seu pacote deverá parecer como a figura 4.

clip_image007

Figura 4

Pacote Incremental

Nesse momento irei utilizar o banco de dado STAGING para criar duas tabelas: CATEGORIA_DESTINO_DELTE e CATEGORIA_DESTINO_UPDATE. O motivo dessas tabelas é realizar as operações de UPDATE e DELETE de uma forma performática.

 1: USE STAGING

 2: GO

 3:

 4: IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[CATEGORIA_DESTINO_DELETE]') AND type in (N'U'))

 5: BEGIN

 6:     SELECT TOP 0 * INTO [dbo].[CATEGORIA_DESTINO_DELETE]

 7:     FROM [DW_EXEMPLO_DCD].[dbo].[CATEGORIA_DESTINO]

 8: END

 9:

 10: IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[CATEGORIA_DESTINO_UPDATE]') AND type in (N'U'))

 11: BEGIN

 12:     SELECT TOP 0 * INTO [dbo].[CATEGORIA_DESTINO_UPDATE]

 13:     FROM [DW_EXEMPLO_DCD].[dbo].[CATEGORIA_DESTINO]

 14: END

No pacote inclua o componente CDC Control Task com as seguintes configurações:

  • Crie ou configure uma conexao para o database de origem.
  • Configure o CDC Control Operation para Get processing range
  • Crie uma nova varial de estado (CDC_state)
  • Crie ou configure uma conexao para o database de destino
  • Selecion a tabela de estado que foi crianda anteriormente pelo pacote inicial ([dbo].[cdc_states])
  • Configure State name – Este nome deve ser igual ao que foi usado no pacote inicial (CDC_State)

clip_image008

Figura 5

Volte ao Control Flow e inclua um Data Flow e ligue-o no CDC Control Task;

Adicione um componente de CDC Source:

  • Configure a conexão para o database de origem;
  • Selecione a tabela [dbo].[CATEGORIA]. Nessa combo box irão aparecer apenas tabelas que estão habilitadas para o uso do CDC;
  • Configure o CDC processing mode para Net. Opção que busca apenas as alterações realizadas na tabela de origem;
  • Selecione a variável CDC_State;
  • Verifique a aba Columns para ter certeza que os dados estão sendo mapiados corretamente;

clip_image010

Figura 6

OBS: O CDC processing mode é a configuração mais importante aqui, pois cada modo de processamento terá um comportamento diferente que espera uma manipulação especifica.

Adicione o componete CDC Splitter.

Agora adicione 3 componetes ADO.NET Destination, um para nova linhas (Insert), outro para linhas atualizadas(UPDATE) e o ultimo para linhas exluidas (DELETE).

O componente para novas linhas (INSERT) você deve configura-lo para carregar diretamente a tabela [dbo].[CATEGORIA_DESTINO].

Os outros componentes devem ser mapeados para inserirem linhas nas tabelas [dbo].[CATEGORIA_DESTINO_DELETE] para linhas exluidas e [dbo].[CATEGORIA_DESTINO_UPDATE] para linhas atualizadas.

Agora o seu Data Flow deve estar parecido com a figura 7

clip_image011

Figura 7

Volte para o Control Flow e adicione dois componentes de Execute SQL Task. Um para realizar o UPDATE na tabela final e outro para realizar o DELETE na tabela final. Dessa maneira temos uma lote sendo executadas em cima de um conjunto de dados e não uma única linha sendo executada em um lote.

Abaixo segue os comandos de UPDATE e DELETE que utilizei.

 1: -- Update

 2:

 3: UPDATE dest

 4: SET

 5: dest.NOME_CATEGORIA = stg.NOME_CATEGORIA

 6: FROM

 7: [dbo].[CATEGORIA_DESTINO] dest

 8: INNER JOIN [STAGING].[dbo].[CATEGORIA_DESTINO_UPDATE] STG

 9: ON STG.ID = dest.ID

 10:

 11: -- Delete 

 12:

 13: DELETE FROM [CATEGORIA_DESTINO]

 14: WHERE ID IN

 15: (

 16: SELECT ID

 17: FROM [STAGING].[dbo].[CATEGORIA_DESTINO_DELETE]

 18: )

Por ultimo temos que atualizar nosso controle do CDC. Mais uma vez utilize o CDC Control Task para realizar essa tarefa e altera a opção CDC control operation para Mark processed range.

clip_image012

Figura 8

Inclui também um componente para limpar as tabelas do STAGING realizando um TRUNCATE nas tabelas.

O seu pacote deve estar parecido com a figura 9

clip_image013

Figura 9

Executando os Pacotes

Como já temos uma carga inicial com 3 registros vamos executar o pacote Inicial para realizar a carga.

clip_image014

Figura 10

Depois de executar o pacote Inicial podemos observar que a tabela cdc_states contem a seguinte informação.

clip_image015

Figura 11

Agora se executarmos o pacote incremental nesse momento não teremos nenhuma informação nova. Por isso vamos manipular alguns dados.

 1: USE EXEMPLO_CDC

 2: GO

 3: INSERT INTO dbo.CATEGORIA VALUES

 4: ('CATEGORIA 4')

 5: ,('CATEGORIA 5')

 6: ,('CATEGORIA 6')

 7: GO

 8: UPDATE dbo.CATEGORIA set NOME_CATEGORIA = 'CATEGORIA ALTERACAO 1'

 9: WHERE ID = 1

 10: UPDATE dbo.CATEGORIA set NOME_CATEGORIA = 'CATEGORIA ALTERACAO 2'

 11: WHERE ID = 2

 12: DELETE FROM dbo.CATEGORIA WHERE ID = 3

Depois de executar os comandos acima, vamos executar o pacote incrementar.

clip_image016

Figura 12

Fazendo uma consulta no seu destino podemos ver que os dados estao corretamente carregados de uma forma simples e eficiente.

clip_image017

Figura 13

Espero poder ajudar assim como o post original me ajudou. Eu literalmente traduzi e incrementei algumas coisas, mas os créditos são do Matt Masson. Para ver o post original acesse http://www.mattmasson.com/index.php/2011/12/cdc-in-ssis-for-sql-server-2012-2/

Obrigado!

This is Tiago Balabuch's website, and this is a bit of copy about him. He is enthusiast, fascinated, passionate, fanatic by SQL Server and in the off-hours a Data Engineer who loves what he does and he is traveling in the cloud and surfing on the wave of the moment called Azure! Originally from Brazil and with encouragement from family and friends, Tiago moved to Europe in 2017 where lives in Ireland. In addition to being a data engineer, he is also active speaker in the SQL PASS events and keeps up to date on the key technologies and technical certifications. Tiago hold these certification MCT, MCSE - Data Management and Analystics, MCSA - SQL Server 2016/2014/2012. Simply psychedelic and manic he is just one more freak who likes SQL Server and its new features ...

Responda