Fala grande amigo, tudo blz? Nossa série está bombando, gostaria de dizer que estamos próximo do fim, mais ainda possuímos um grande trilha para traçar...
Hoje vamos falar sobre o componente OLE DB Command. Esse componente é usado principalmente para um conjunto de transformações que vão acontecem em cada linha a partir de um comando SQL que será executado.
Vamos começar então? Gosto mais de coloca a mão na massa do que ficar escrevendo historinha...'
Antes de avançar para o código, vamos fazer uma breve introdução de como iremos trabalhar nesse post.
1 - Criação de um projeto SSIS - Sempre que trabalhamos com SSIS, existe a necessidade de realizar a criação de um projeto no Visual Studio, caso tenha criado nos posts anteriores, pode utilizar sem problemas, porém caso não tenha trate de criar pois é um item obrigatório! Dentro desse projeto vamos armazenar todos os pacotes de ETL;
2 - Criação de um pacote de ETL - Quando falamos em pacotes de ETL, estamos nos referindo ao conjunto de fluxos que será usado para trafegar com a nossa informação da sua origem até seu destino. Recomento que caso esteja acompanhando nossa série, crie um pacote para cada item, fica mais fácil o entendimento;
3 - Criando/Configurando as conexões - Vamos utilizar nesse nosso post duas conexões do tipo OLE DB pois iremos consumir e gravar dados do Microsoft SQL Server, ou seja, Source (1) e Destinantion (2) irão apontar para o mesmo local, a única diferença será a tabela que será consumida e iremos gravar em dois destinos diferentes (um será uma tabela fisica e o outro uma tabela temporária). Esse é o passo onde definimos as conexão;
4 - Criando nosso Fluxo de Dados
4.1 - Componentes que serão usados - Nesse primeiro exemplo, vamos trabalhar com quatro componentes: Source Assistant (1), responsável pela conexão com a origem. Vamos usar um componente Conditional Split (1) para direcionar quais registros serão eliminados e quais serão atualizados, o componente Derived Column (1) pois para os registros que serão atualizados iremos criar uma nova descrição para a coluna DS_CARGO que deverá obedecer a seguinte regra (CD_CARGO + ' - ' + DS_CARGO), vamos usar também o componentes Aggregate (2) justamente para transformar as linhas em um único registro e pra finalizar o componentes OLE DB Command (2) onde um será usado para atualizar os dados e o outro para eliminar;
4.2 - Criando nosso destino dos dados (Tabela) - Vamos criar uma tabela para receber os dados;
5 - Visualizando os dados carregados - E por último, vamos visualizar como que ficaram os dados carregados em nossa tabela.
Bora começar então!
1 - Criando um Projeto no SQL Server Integration Services
Vou considerar que você não tem nenhum projeto criado no Visual Studio do SQL Server Integration Service. Um ponto bem importante desse estudo é que não irei falar como que você realiza a configuração do seu ambiente, entenderei que você já tenha feito isso em outro momento.
Para criar um projeto é bem simples. Vamos abrir o Visual Studio e seguir os próximos passos.
(1) Selecionar File;
(2) New;
(3) Project;
(4) Caso tenha um projeto criado basta clicar em Open Project.
Após clicar em New Project, a tela abaixo será apresentada.
(1) Escolher Integration Services;
(2) Integration Services Project;
(3) Informar um nome para o Projeto;
(4) Definir o local (diretório) que o projeto será gerado;
(5) Informar uma nomenclatura para a sua solução;
(6) Caso queira que um diretório para o projeto seja criado basta marcar essa opção;
(7) Clicar em OK para realizar a criação;
2 - Criando nosso Pacote de ETL
Primeiro ação será a criação de um pacote do SSIS. Essa criação é bem simples, acompanha comigo.
(1) Clicar com o botão direito do mouse sobre SSIS Package;
(2) Clicar em New SSIS Package;
Além dessa possibilidade, outras ações podem ser realizadas.
Importar um Arquivo dtsx - basta clicar em Add Existng Package;
Realizar o Upgrade dos seus pacotes - basta clicar sobre Convert Deployment Model ou Upgrade All Package;
Realizar um Import and Export - basta clicar sobre SSIS Import and Export Wizard;
Ordenar seus pacotes - basta clicar em Sort by name.
Após a criação do nosso arquivo dtsx, vamos para o desenho do nosso fluxo.
(1) Nome do arquivo dtsx;
Para alterar basta clicar sobre ele e apertar o botão F4 e em file name informar o nome do seu pacote.
Ou clicar sobre o pacote e apertar F2
(2) Aba Control Flow;
(3) Inserir um componente de Data Flow Task;
(4) Realizar duplo clique ou clicar em Data Flow.
Ao clicar em Data Flow, vamos começar a brincadeira! Dessa vez vamos fazer iguais aos filmes de Hollywood começando do final. Apresento para você como que será o resultado final desse nosso trabalho (1).
Vamos trabalhar então para produzir mais esse conteúdo.
3 - Criando as Conexões
Primeiro passo será realizar a configuração das nossas conexões.
(1) Aba Connection Manage;
(2) Clicar com o botão direito do mouse e escolher New OLE DB Connection;
Após selecionar esse tipo de conexão, vamos partir para configuração.
(1) Informar um nome para a conexão;
(2) Primeira configuração será na aba General;
(3) Selecionar o local onde está o nosso arquivo;
(4) Configurações referente ao nosso arquivo (Se ele é delimitado ou não, se possui um qualificador de texto, se a primeira linha do arquivo contém ou não o cabeçalho das colunas etc);
(4) Clicar em OK para confirmar.
4 - Criando o Fluxo de Dados
Na aba Control Flow, vamos selecionar seguir os seguintes passos.
(0) Podemos observar que a conexão criada está configurada e disponível;
(1) Em SSIS Toolbox o componente Data Flow Task;
(2) Sobre o componente Data Flow Task, vamos clicar com o botão direito;
(3) Clicar em Rename.
Na próxima tela conseguimos visualizar o esqueleto (1) do nosso motor.
4.1 - Configurando o Componente Source Assistant
Conforme imagem anterior, vamos fazer uso de 3 componentes! Um para a nossa origem de dados, uma para o destino dos dados e um que permite que façamos um relacionamento entre os nossos datasetts, ou seja, ele é o objetivo desse post.
Acompanha ai como que é simples!
(1) Em SSIS Toolbox, vamos selecionar o componente Source Assistant;
(2) Feito isso a tela ao centro será carregada;
(3) Como nossa origem será uma tabela do nosso banco de dados, vamos selecionar SQL Server;
(4) Selecionar a conexão que criamos;
(5) Observe que algumas informações referente a origem dos dados é apresentada;
(6) Clicar em OK para confirmar.
Após a inclusão do componente de Source, vamos selecionar de onde (tabela) os dados deverão ser carregados.
(1) Vamos clicar duas vezes sobre o componente OLE DB Source, vamos trabalhar na na aba Connection Manager;
(2) Selecionar a conexão que criamos anteriormente;
(3) Nesse passo também é possível realizar a criação de uma nova conexão;
(3.1) Nesse ponto podemos selecionar se a nossa origem será uma tabela ou view, caso escolha, no passo seguinte você deverá selecionar em uma lista qual a origem e também pode ser selecionado um script, caso seja escolhida essa opção, no passo seguinte você deverá informar o script de consulta;
(4) Selecionar a origem, ou tabela ou script, depende do passo anterior;
(5) É possível visualizar uma prévia dos dados;
(6) E clicar em OK para finalizar.
4.2 - Configurando o Componente Conditional Split
(1) Em SSIS Toolbox, vamos selecionar o componente Conditional Split;
(2) Feito isso a tela ao centro será carregada;
Após incluir o componente, vamos realizar sua configuração.
(1) Duplo clique sobre o componente Conditional Split;
(2) Área em que definimos as regras que serão aplicadas no fluxo dos dados;
(3) Nome que será dados para os fluxos de output;
(4) Regras criadas.
4.2 - Configurando o Componente Aggregate
Vamos agora incluir em nosso fluxo um passo para realizar a agregação dos dados.
(1) Em SSIS Toolbox, vamos selecionar o componente Aggregate;
(2) Vamos clicar sobre o componente Conditional Split e após isso realizar a ligação com o componente Aggregate;
(3) Ao realizar o relacionamento entre os componentes, uma tela ao centro será carregada para que seja selecionado o fluxo de dados, esse fluxo foi o passo anterior que realizamos;
(4) Após selecionar, basta clicar em OK para confirmar.
Após estabelecer a ligação entre os componentes, vamos configura o componente Aggregate.
(2) Duplo clique sobre o componente;
(3) Vamos selecionar a coluna DS_CARGO;
(4) Vamos definir qual a operação será realizada sobre ele. Nesse caso vamos aplicar uma agregação para evitar valores duplicados;
(5) Após realizar toda configuração, basta clicar em OK para confirmar.
4.2 - Configurando o Componente Derived Column
Após a configuração do componente de Agregação, vamos realizar a configuração do componente Derived Column.
(1) Em SSIS Toolbox, vamos selecionar o componente Derived Column;
(2) Duplo clique sobre o componente Derived Column e após isso realizar a ligação com o componente Conditional Split;
(3) Ao realizar o relacionamento entre os componentes, uma tela ao centro será carregada para que seja selecionado o fluxo de dados, esse fluxo foi o passo anterior que realizamos;
(4) Após selecionar, basta clicar em OK para confirmar.
Após estabelecer a relação entre os componentes, vamos realizara configuração do componente Derived Column.
(1) Duplo clique sobre o componente;
(2) Nesse momento vamos criar uma nova coluna que será resultado da concatenação das colunas CD_CARGO e DS_CARGO;
(3) Área de definição de regras;
(4) Podemos definir um fluxo em caso de erro (não vamos abordar esse passo nesse post);
(5) Após conclusão da configuração, basta clicar em OK para confirmar.
4.2 - Configurando o Componente OLE DB Command
Pronto! Preparamos o fluxo para entrar na etapa final.
Vamos iniciar agora a configuração do componente OLE DB Command.
(1) Em SSIS Toolbox, vamos selecionar o componente OLE DB Command;
(2) Feito isso ele será carregado na tela.
Após a seleção do componente, bora pra configuração!
(1) Duplo clique sobre o componente e a tela será apresentada. Vamos iniciar a configuração na aba Connection Managers;
(2) No próximo passo vamos selecionar a conexão que iremos criar, em nosso caso possuímos apenas uma. Um ponto de atenção! Em outros componentes, possuíamos a opção de criar uma nova conexão próprio componente, nesse que estamos trabalhando, isso não é permitido, blz?;
Após concluída a configuração da aba Connection Managers, vamos avançar para configurar a aba Component Properties.
(1) Clicar sobre a aba Component Properties;
(2) Em Custom Properties, vamos selecionar a opção SqlCommand e clicar cobre o quadradinho que aparece no final para adicionar o script que estaremos usando;
(3) Informar o script;
(4) Após conclusão da configuração, basta clicar em OK para confirmar.
Vamos simbora guerreiro...
No próximo passo vamos realizar o mapeamento entre as colunas. No nosso exemplo, definimos que todos os registros que possuem o CD_CARGOS igual a 12 deverão ser eliminados.
(0) Clicar sobre a aba Column Mappings;
(1) Objeto do Source e Destination;
(2) Coluna resultado do processo de Agregação no componente Aggregate;
(3) Entrada definida no script de DELETE que definimos na aba Component Properties;
(0) Grid gerado a partir do relacionamento entre os componentes.
Lembrando que a definição das colunas no componente de OLE DB Command, seguem a sequência em que é definida a colocação dos símbolos de interrogação.
O primeiro símbolo será o Param_0 no mapeamento, o segundo será o Param_1 e assim por diante.
Concluída a etapa anterior, vamos avançar em nosso fluxo.
(1) Em SSIS Toolbox vamos adicionar um novo componente Aggregate;
(2) Selecionar as colunas CD_CARGO e DS_CARGO e definir que estaremos usando uma operação de GROUP BY;
(3) Grid com as colunas selecionadas e com a regra que foi aplicada;
(4) Após conclusão da configuração, basta clicar em OK para confirmar.
Feito isso, vamos adicionar mais um componente de OLE DB Command para realizar a operação de atualização da descrição do cargo casos onde o CD_CARGO for igual a 11.
Vem comigo, é bem simples!
(2) Duplo clique sobre o componente OLE DB Command;
(3) Na aba Connection Managers, vamos iniciar a configuração da conexão;
(4) Em Connection Manager, vamos selecionar a conexão que criamos
(5) Após conclusão da configuração, basta clicar em OK para confirmar.
Esse passo será exatamente idêntico ao que realizamos anteriormente em que criamos o processo de eliminação dos registros com CD_CARGO igual a 12.
Vamos lá, avança aqui comigo...Após concluída a configuração da aba Connection Managers, vamos avançar para configurar a aba Component Properties.
(2) Clicar sobre a aba Component Properties;
(3) Em Custom Properties, vamos selecionar a opção SqlCommand e clicar cobre o quadradinho que aparece no final para adicionar o script que estaremos usando;
(4) Informar o script;
(5) Após conclusão da configuração, basta clicar em OK para confirmar.
Seguindo a mesma linha de raciocínio, esse passo também é idêntico ao anterior, acompanha aqui...
No próximo passo vamos realizar o mapeamento entre as colunas. No nosso exemplo, definimos que todos os registros que possuem o CD_CARGOS igual a 11 deverão ter a coluna DS_CARGO preenchida com a concatenação das colunas CD_CARGO + '-' + DS_CARGO. Veja ai com fazer...
(0) Clicar sobre a aba Column Mappings;
(1) Objeto do Source e Destination;
(2) Coluna resultado do processo de Agregação no componente Aggregate;
(3) Entrada definida no script de UPDATE que definimos na aba Component Properties;
(0) Grid gerado a partir do relacionamento entre os componentes.
Lembrando que a definição das colunas no componente de OLE DB Command, seguem a sequência em que é definida a colocação dos símbolos de interrogação.
O primeiro símbolo será o Param_0 no mapeamento, o segundo será o Param_1 e assim por diante.
Concluímos a configuração dos nossos dois fluxos utilizando o componente OLE DB Command.
Para finalizar nosso post, vamos verificar como que fica a execução do nosso fluxo.
4.4 - Configurando o Componente Execute SQL Task
Antes de executar o fluxo, realizei uma consulta em nossa base de dados para levantar alguns informações.
Analisem comigo o item (2). Nele conseguimos observar duas situações antes de iniciar o processamento do nosso ETL.
1 - Existem três grupos de dados (três cargos, CD_GARGO 11, 12 E 13), com um total de 5569 registros cada;
2 - Não existe nenhum registro que possua na coluna DS_CARGO o código concatenado com a descrição.
Agora vamos executar o fluxo e analisar o resultado.
(1) Fluxo executado;
(2) Consulta dos dados antes da execução;
(3) Consulta dos dados pós execução.
Com isso meu chapa, conseguimos verificar os dois pontos destacados acima!
1 - Antes da execução, existiam três grupos de dados (três cargos, CD_GARGO 11, 12 E 13), com um total de 5569 registros cada, após a execução passou a ter apenas um
2 - Da mesma forma do item anterior, não existia nenhum registro que possuísse na coluna DS_CARGO o código concatenado com a descrição, após a execução passou a ter.
Os arquivos usados até aqui estão disponíveis em:
Pacote SSIS - OLE DB Command
13_Atualiza_Elimina_Registros_OLE_DB_Command.dtsx
5 - Conclusão
Muito bem grande amigo, terminamos assim mais super post.
Nesse grande post, destacamos o componente OLE DB Command. Um componente que podemos usar para atualizar, eliminar e até para incluir registros.
Porém um ponto que não podemos deixar de falar é que esse componente possui o comportamento de realizar ações linha a linha, então, diante disso devemos analisar a sua utilização para que não seja um tiro no pé dentro do seu fluxo.
Espero que tenha gostado e nos encontramos nos próximos posts!
Grande abraço e fique com Deus!