SQL Server Integration Services, Trabalhando com componente Derived Column

E ai amigão tubo bem com essa força???? Pronto para uma bateria de estudos?? Ótimo! Simbora então!

No post anterior, comentei que estaríamos abordando hoje um componente chamado Derived Column, esse sim dá pra fazer bastante coisa. Lembro que comentei que com ele é possível criar novas colunas, limpar dados (retirar espaços, caracteres indesejados), trabalhar apenas com um pedaço de uma informação, combinar duas ou mais colunas para fazer apenas uma, em resumo, ele será um amigo e tanto quando o assunto for ETL!

Confesso que é um dos componente que mais utilizo nos projetos que desenvolvo então posso dizer que realmente ele é bem útil.

Vamos fazer agora como de costume, 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. A conexão para o Source será do tipo Flat File pois iremos consumir um arquivo CSV e a do Destination, será com o Microsoft SQL Server, pois vamos gravar o resultado da nossa carga dentro de uma tabela. 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 Três componentes: Source Assistant, responsável pela conexão com a origem. Vamos importar um arquivo CSV, iremos usar também o componente Derived Column pois vamos realizar três ações, (1) converter a coluna QT_VAGAS que está como String para Inteiro, (2) utilizar o comando replace para remover um peçado da informação que vem no arquivo e (3) combinar duas colunas para formar uma única, tudo muito simples, não fique nervoso ainda!! E por último o componente Destination Assistant que será usado para definir o destino dos dados.

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.

Mão a obra! Pois camarão que dorme a onda leva...

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 Flat File 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).

Agora vamos para outra aba.

(1) O nome que você informou na tela anterior é carregado em todas as abas seguintes, não precisa informar;

(2) Vamos configurar a aba Columns;

(3) Selecionar o delimitador de linhas;

(4) Selecionar o delimitador de colunas;

(5) Uma prévia dos dados. Observe que o cabeçalho foi carregado pois na tela anterior informamos que a primeira linha do arquivo contém o cabeçalho;

(6) Caso nenhuma configuração a mais precise ser feita, podemos concluir a configuração clicando em OK.

Seguindo nossa configuração, vamos avançar mais uma aba...

(1) O nome que você informou na tela anterior é carregado em todas as abas seguintes, não precisa informar;

(2) Vamos configurar a aba Advanced;

(3) Nome da colunas;

(4) Nesse passo conseguimos visualizar informações referente ao nome da coluna, datatype, tamanho, tudo isso pode ser alterado;

(5) Nesse ponto é possível incluir ou eliminar uma coluna;

(6) Caso nenhuma configuração a mais precise ser feita, podemos concluir a configuração clicando em OK.

Agora vamos para o último ponto da configuração. Na aba Preview como o próprio nome diz, é uma prévia dos dados e além disso você pode também definir a quantidade de linhas que serão descartadas.

(1) O nome que você informou na tela anterior é carregado em todas as abas seguintes, não precisa informar;

(2) Vamos configurar a aba Advanced;

(3) Podemos definir a quantidade de linhas que serão descartadas;

(4) Prévia dos dados;

(5) Clicar em OK para finalizar a configuração.

A próxima configuração será referente ao nosso destino, ou seja, onde os dados serão gravados.

(1) Aba Connection Manage;

(2) Clicar com o botão direito do mouse e escolher New OLE DB Connection;

Como destino, escolhemos o Microsoft SQL Server, acompanha comigo.

(1) Na aba Connection Manager, vamos clicar com o botão direito e selecionar OLE DB Connection Manager. Na tela que será apresentada, vamos clicar em New;

(2) Selecionar Native OLE DB\SQL Server Native Client 11.0;

(3) Informar o nome do servidor\instância;

(4) Selecionar o método de conexão;

(5) Selecionar o banco de dados. Clicando na setinha pra baixo é possível visualizar todos os bancos existentes em nosso servidor;

(6) Clicar em Test Connection para validar a conexão (Sempre comento que esse passo é desnecessário, pois ela já foi validada no passo anterior);

(7) Clicar em OK para finalizar.

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.

Conexões criadas, agora vamos montar 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 tratamento dos dados, ou seja, ele é o objetivo desse post.

(1) Em SSIS Toolbox, vamos selecionar o componente Source Assistant;

(2) Feito isso a tela ao centro será carregada;

(3) Como nossa origem será um arquivo, vamos selecionar Flat File;

(4) Selecionar a conexão que criamos;

(5) Observe que algumas informações referente a origem do arquivo é apresentada;

(6) Clicar em OK para confirmar.

E agora a nossa cereja do bolo. Vamos falar um pouco de como podemos trabalhar com esse componente fantástico chamado Derived Column.

4.2 - Configurando o Componente Derived Column

(1) Nome do componente;

(2) Nesse passo podemos selecionar uma variável ou parâmetro e até mesmo selecionar uma coluna definida em nosso Source;

(3) Esse componente traz algumas funções que podem ser usadas durante a sua manipulação;

(4) Área onde realizamos a criação dos nosso tratamentos;

(5) Podemos definir algum fluxo caso ocorra algum erro durante a aplicação da regra definida;

(6) E caso esteja tudo OK, basta clicar para finalizar.

Uma breve explicação dos comandos acima.

Derived Column: QT_VAGAS_t (coluna que será criada)
Script: (DT_I4)QT_VAGAS
Explicação: (DT_I4) comando para definir que uma coluna será o tipo Integer

Derived Column: DS_ELEICAO_t (coluna que será criada)
Script: (DT_STR,200,1252)(REPLACE(DS_ELEICAO,"Eleições Municipais 2020","Eleições Municipais"))
Explicação: (DT_STR,200,1252) quando trabalhamos com colunas do tipo String, a definição requer 3 parâmetros.
DT_STR - indica que a coluna será do tipo String (Varchar)
200 - tamanho do campo
1252 - encoding do dado

Derived Column: NM_TIPO_ELEICAO_t (coluna que será criada)
Script: (DT_STR,200,1252)(CD_TIPO_ELEICAO + " - " + NM_TIPO_ELEICAO)
Explicação: (DT_STR,200,1252) quando trabalhamos com colunas do tipo String, a definição requer 3 parâmetros.
DT_STR - indica que a coluna será do tipo String (Varchar)
200 - tamanho do campo
1252 - encoding do dado

Na tela abaixo apresento como que seria esse passo referente ao tratamento de erro. Esse passo pode ser direcionado para um banco ou até mesmo para um arquivo de log.

(1) Clicar em Configure Error Output;

(2) Tela que será carregada;

(3) Colunas referente as que foram criadas com os tratamentos;

(4) Clicar em OK para finalizar.

VERIFICAR A POSSIBILIDADE DE INCLUIR UM TESTE PARA O FLUXO DO ERRO

Agora vamos fazer a inclusão do nosso Destination.

4.3 - Configurando o Componente Destination Assistant

Vamos abordar nesse momento como que seria a configuração do nosso componente Destination Assistant.

(1) Em SSIS Toolbox, vamos selecionar o componente Destination Assistant;

(2) Feito isso a tela ao centro será carregada;

(3) Como nossa origem será um arquivo, vamos selecionar SQL Server;

(4) Selecionar a conexão que criamos;

(5) Observe que algumas informações referente ao destino do arquivo é apresentada;

(6) Clicar em OK para confirmar.

Após a configuração inicial, vamos selecionar a tabela em que os dado estarão sendo armazenados.

(1) Após dois cliques no componente Destination, uma tela semelhante a de baixo será carregada, vamos clicar em Connection Manager;

(2) Selecionar a conexão;

(3) Caso não queira usar a conexão existente, existe a possibilidade de criar uma nova;

(4) Definir como que os dados serão carregados, podendo ser em bloco (Table or View - fast load) ou registro a registro (Table or View);

(5) Selecionar a tabela que será carregada;

(6) Da mesma forma como ocorre com o item (3), podemos criar uma nova tabela para receber os dados caso não possua uma já criada;

(7) É possível visualizar os dados existentes na tabela (apenas uma amostra é apresentada);

(8) Ainda não é o momento de clicar em OK, vamos avançar para a aba Mappings para realizar o mapeamento das colunas que irão receber os dados;

Observem que quando clicamos na aba Mappings, as colunas já são carregadas com o mapeamento feito. Isso ocorre justamente porque por default, esse mapeamento é feito baseado por igualdade de nomes. Mais ele pode ser alterado a qualquer momento.

(1) Aba Mappings;

(2) Colunas mapeadas automaticamente;

(3) Colunas da Origem;

(4) Colunas do Destino;

(5) Colunas que criamos a partir do componente Derived Column;

(6) Clicar em OK para concluir;

Para finalizar nossa configuração, vamos incluir um componente que será usado por limpar sempre o nosso destino antes da carga.

4.4 - Configurando o Componente Execute SQL Task

Vamos dar um passo atrás e retornar em nosso Control Flow, para fazer isso basta clicar na aba acima e seguir os passos abaixo...

(1) Clicar na aba Control Flow, depois clicar em SSIS Toolbox e selecionar o componente Execute SQL Task;

(2) Nome do componente;

(3) Nesse passo vamos configurar apenas a aba General do componente Execute SQL Task;

(4) Informar o nome desse passo dentro do nosso fluxo;

(5) Selecionar a conexão, vamos usar a que criamos anteriormente;

(6) Incluir um script de TRUNCATE TABLE;

(7) Clicar em OK para finalizar.

Pronto, configuração realizada, vamos agora executar e visualizar os dados.

(1) Fluxo dos dados;

(2) Vamos conectar em nosso servidor;

(3) Selecionar nosso banco de dados;

(4) Tabela em que os dados foram carregados;

(5) Comando de consulta dos dados.

Os arquivos usados até aqui estão disponíveis em:

https://github.com/dbaassists/ssis

5 - Conclusão

Ufa, mais um desafio concluído! Então meu amigo, o que achou? Isso foi apenas uma breve introdução do que podemos fazer com o componente Derived Column. Como destaquei, utilizo bastante ele durante a criação dos nosso fluxos, criando colunas novas, tratando quando algum registro for nulo e que precisa ter um valor, é muito utilizado para aplicação do comando Case When.

Agora com esse ponto superado, vamos para o próximo. Em nosso próximo post vou falar um pouco sobre o componente Union All e se eu fosse você não perdia por nada!

Até o próximo encontro, grande abraço e fique com Deus!