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):
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
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
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
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.
Figura 1
Proxima tarefa é iniciar seu Data Flow que nesse exemplo é bem simples.
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
Figura 3
Seu pacote deverá parecer como a figura 4.
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)
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;
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
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.
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
Figura 9
Executando os Pacotes
Como já temos uma carga inicial com 3 registros vamos executar o pacote Inicial para realizar a carga.
Figura 10
Depois de executar o pacote Inicial podemos observar que a tabela cdc_states contem a seguinte informação.
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.
Figura 12
Fazendo uma consulta no seu destino podemos ver que os dados estao corretamente carregados de uma forma simples e eficiente.
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!
Tiago Balabuch
Related Posts

SSIS – Proxy Account Permission
A utilização de Proxy Account para execução de pacotes SSIS dentro do SQL Server Agent é um procedimento para...

Eu devo utilizar as DMV?
Você já deve ter ouvido falar nas DMV, mas voce já utilizou alguma? Primeiramente, DMV é o nome mais comum para...