Neste artigo, que será dividido em duas partes, iremos explicar como configurar o pacote de carga inicial do CDC (Change Data Capture) em uma tabela de um banco de dados do SQL Server para acompanhar o rastreamento dos dados, assim utilizaremos o SSIS (SQL Server Integration Services). Este é um serviço do SQL Server que está disponível nas edições Standard ou superiores para integrações ETL (Extract, Transform e Loading).

Saiba mais sobre o CDC aqui no blog da CDB o post Para que Server e como Configurar o Change Data Capture.

Para o desenvolvimento precisaremos ter o Data Tools instalado no Visual Studio. Para saber como instalar e configurar o SSIS recomendamos a leitura do post Como Debugar um Código T-SQL?.

Até esta data não há versão do Data Tools disponível para o Visual Studio 2022, logo utilizaremos aqui a versão 2019.

Criando a Solução no Visual Studio

Ao abrir o Visual Studio 2019 podemos procurar por Integration e será listado todas as opções de projeto. Escolher o “Integration Services Project”.

Seguir o Wizard do Visual Studio, especificar o nome do projeto, caminho etc. Depois de tudo preenchido abrirá a tela:

Pacotes a serem criados na solução

Para esta solução precisaremos de dois pacotes, o primeiro cujo nome do arquivo será “inicial.dtsx” e executará a primeira carga. Já o segundo chamado “diferencial.dtsx” será o pacote de cargas diferenciais e será executado periodicamente. O pacote “diferencial.dtsx” será mostrado sua criação em detalhes na parte 2 deste post.

Se for necessário renomear o pacote no Solution Explorer, selecione o pacote e tecle F2, isso vai permitir que o nome seja alterado. Após alterado o nome aparecerá uma mensagem para confirmar a alteração.

Assim que a solução for criada, o primeiro pacote também aparecerá, este será o nosso “Inicial.dtsx”.

Clique com o botão direito na pasta SSIS Packages e então New SSIS Packages.

Ao final desses passos, seu resultado será similar a imagem abaixo:

Distribuição dos Componentes

Control Flow

No Control Flow incluiremos somente 3 componentes, 2 CDC Control Task e 1 Data flow Task dispostos da seguinte forma:

Os componentes CDC Control Task são utilizados para marcar o início e o fim da leitura do CDC.

Por este motivo temos dois componentes iguais, o primeiro lê a posição de início da carga, o último grava na tabela o fim da leitura deixando pronto para a próxima vez que o pacote for executado.

No primeiro CDC Control Task vamos colocar o nome de CDC Marca Início e vamos configurar conforme abaixo:

• Primeiro campo: clicar em “New…” e criar uma conexão “Ado.Net” ao banco de dados que tem o CDC habilitado. Neste exemplo, usaremos a máquina local que é/tem o SQL Server 2019, usando uma autenticação Windows e apontando para o banco de dados Cliente;

• CDC Control Operation: escolha a opção “Mark initial load start”. Esta é a opção que irá iniciar a coleta de dados do CDC;

• Variable containing the CDC state: clique em New e deixe os campos com a sugestão do DataTools;

• Connection manager for the database Where the state is storage: neste exemplo, vamos utilizar a mesma conexão que criamos acima;

• Table to use for storing state: clique no botão New, aparecerá uma tela com o script de criação da tabela, mantenha e clicar em Run;

• State name: na lista escolha o CDC_state;

No segundo CDC Control Task vamos colocar o nome de CDC Marca Fim e a configuração é igual ao do primeiro CDC Control Task, mudando somente o campo CDC control operation. No mesmo deve ser escolhida a opção “Mark initial load end”. Assim, ao final da execução deste pacote será guardado o último registro lido pelo CDC.

Data Flow

No Data Flow vamos incluir 5 componentes, 1 CDC Source, 1 CDC Splitter e 3 ADO Net Destinations.

No Data Flow incluiremos a maioria das transformações que faremos nos dados, neste exemplo não teremos muitas transformações, vamos apenas separar os tipos de transações e gravar em tabelas diferentes.

O CDC Source tem a função de carregar os dados do CDC e jogar para o fluxo do pacote. Abaixo segue as configurações:

• Ado. Net connection manager: novamente vamos utilizar a conexão que criamos quando configuramos o primeiro CDC Control Task;

• CDC enable table: este campo listará todas as tabelas que têm o CDC habilitado, escolha uma delas;

• Capture instance: neste campo aparecerá somente uma tabela;

• CDC processing mode: indique a opção “All with old values.” Esta opção carregará todos os registros pendentes desde a última vez que o CDC foi lido;

Variable containing the CDC state: indique a variável criada quando configuramos o CDC Control Task.

 A tela de configuração do seu CDC source ficará similar a imagem abaixo:

Curiosidade – se você clicar em colunms, será listado mais colunas do que há na tabela. Estas são colunas de controle do CDC que são usadas somente pelos componentes do CDC no Data tools, fisicamente elas não existem na tabela.

O próximo componente é o CDC Splitter que não precisa ser configurado. Ele recebe os dados do CDC Source e separa os tipos das transações.

Na configuração dos outros 3 componentes mudarão somente a tabela de destino.

Connection manager: utilizaremos a mesma conexão utilizada nos componentes anteriores;

Use a table or view: indicaremos a tabela correspondente de saída do componente que também indica o tipo da transação. Na imagem ao lado estamos direcionando a saída “InsertOutput” para a tabela Customers_INS.

Os outros 2 componentes utilizam a mesma conexão, mudando somente a tabela conforme a saída do componente, ou seja, a saída “UpdateOutput” será direcionada para a tabela Customers_Upd e a saída “DeleteOutput” será direcionada para a tabela Customers_del.

Conclusão

Nesta primeira parte deste post, aprendemos sobre a criação de conexões entre as marcas de transações para rastreamento dos dados.

Na segunda parte será apresentado como fazer a carga de dados diferenciais das transações.

Fique ligado!

Até o próximo post pessoal! 😊