Manual de Criação de ETL

Manual de Criação de ETL


Este artigo tem o intuito de orientar na criação do Serviço de ETL em um SQL Server.
Para a criação desse serviço, é necessário um servidor Windows Server com SQL Server Enterprise e o Serviço de SSIS (SQL Server Integration Services) instalado.  
O pacote ispac da carga do ETL é fornecido pela Simply e será disponibilizado para o cliente por e-mail ou FTP.  

Para a configuração, você precisará fornecer a versão de seu SQL e a versão do S-Works para a configuração correta do pacote ispac. 

Instalação do pacote ispac 

  1. Para a instalação do pacote dentro do SQL Server será necessário um usuário de Windows com permissão para acesso no banco de dados da aplicação, preferencialmente com Sysadmim e um usuário de SQL com permissão de Leitura no banco de dados da aplicação. 
  2. Dentro do SQL Server, em Integration Server Catalog, crie um novo catálogo para a instalação do ispac de acordo com a imagem abaixo: 


 

 


















  1. Marque a opção: Enable CLR Integration (Tradução: Habilitar Integração CLR), conforme a imagem abaixo. Crie uma senha para a encriptação de chave. Guarde bem esta senha.
Após a criação da senha, marque a opção Enable this server as SSIS scale out master (Tradução: Habilite este servidor como SSIS scale out master).



 


 















  1. Clique em OK e o catálogo ficará disponível.

 










  1. Após a criação do catálogo, será feita a instalação do arquivo ispac. Para isso, clique duas vezes no arquivo disponível e em seguida, clique no botão next (próximo).

 




 













  1.  Mantenha selecionada a opção Project Deployment File e clique em next.





 














  1.  Mantenha selecionado o SSIS in SQL Server e clique em next.






 

 












  1. Nesse passo será realizada a configuração do path onde será instalado o path do ispac. Em server name, clique em Browse e selecione o nome do servidor do sql.


















  1. Em Path, clique em Browse e logo após em New Folder.








 








  1. Coloque o nome de sua preferência para o Folder e clique em ok. Em seguida, clique em next.








 










  1. Clique em Deploy e aguarde finalizar. Depois clique em close. 



 


 
 




 
Configuração do Environment  

Neste passo a passo, será realizada a configuração do Environment que precisa ser usado na execução do ETL.

  1. Clique em Create environment.













 



  1. No Campo Environment name, coloque um nome de sua preferência e clique em OK.


 











  1. Acesse as propriedades do Enviroment criado:



 
 


  1. Neste passo, serão criadas 6 variáveis necessárias para esta execução:
  1. DestinationOutPutType: é o tipo de saída dos dados manipulados pelo BI (0 – Arquivo, 1 – Banco de Dados, 2 – Arquivo e Banco de Dados) 
  1. DestinationBIDataBase: conection string da base de dados de destinação. Observação: ela deve ser sempre configurada mesmo que o tipo de saída seja 0 – Arquivo. Essa base deve OBRIGATORIAMENTE chamar BI_SWORKS
           Exemplo de configuração: Data Source=TPRB10166;Initial 
           Catalog=BI_SWORKS;Provider=SQLNCLI11.1;Integrated Security=SSPI;Auto Translate=False;
  1. DestinationDataBase: Configurar com o mesmo valor de DestinationBIDatabase
  1. ExecutableBCP: é um arquivo executável do SQL chamado BCP.exe que normalmente fica em C:\Program Files\Microsoft SQL Server\Client SDK\ODBC\130\Tools\Binn\bcp.exe, mas dependendo de forma como foi instalado o SQL pode estar em outra pasta ou unidade. 
  1. OutputDir: Caminho onde os arquivos do ETL serão salvos. Pode ser salvo localmente ou em um caminho de rede, sempre ficar atento para esta pasta ter todas as permissões possíveis de acordo com sua política de acesso. 
Observação: : ela deve ser sempre configurada mesmo que o tipo de saída seja 1 – Banco 
  1. SourceDatabaseDBName: Nome da base de dados onde serão extraídos os dados: 
  1. SourceDatabasePassword: Senha do usuário de banco de dados. 
  1. SourceDatabaseServer: Ip do servidor de banco de dados. 
  1. SourceDatabaseUserID: Usuário para execução do ETL, este usuário especificamente precisa ser um usuário de SQL.

  1. Todos os campos serão do tipo string. Na variável senha, marque a opção de acordo com a imagem abaixo:




 
























  1. Com  o Environment configurado, é preciso criar as referências dele dentro do projeto do ETL. Para isso, clique em Configure:


















  1. Clique em References e depois Add 7.






 

 










  1. Selecione seu Environment e clique em ok.


  1.  Agora é necessário referenciar em Parameters as variáveis do Environments qu foi configurado. Clique em Parameters:
 
 













  1. É necessário configurar os Campos em vermelho da imagem abaixo. O primeiro deles será o Enable BCP. Clique no pequeno quadrado em vermelho.
 
 

 

 









  1. Selecione o Evorionment, localize o Enable BCP e clique em ok.

 
 











  1. Agora repita o mesmo processo para os outros campos conforme abaixo:


Output Dir
 


















SourceDatabaseDBName





 















SourceDatabasePassword

 
 















SourceDatabaseServer


 
 













SourceDatabaseUserID





Configuração de Job’s Agent’s para execução do ETL 

1. Nesta etapa, você configurará dois Job's Agent's para execução do ETL. Um deles será configurado com as cargas de IL que são as cargas iniciais do ETL  e será executada somente uma vez. O outro job será configurado com as cargas PR que são as atualizações do ETL e precisa ser gerada diariamente.

1.1 As cargas a serem configuradas para a IL são: 
  1. IL_RespostaAtividadeManual 
  1. IL_TipoSolicitacaoOrquestrada 
  1. IL_DefinicaoTarefa 
  1. IL_TarefaExecutada 
  1. IL_Fluxo 
  2. IL_TipoAtividadeManual 
  1. IL_StatusTarefa 
  1. IL_Milestone 
  1. IL_TipoMetodoTarefa 
  1. IL_API 
  1. IL_Metodo 
  1. IL_Usuario 
  1. IL_Processo 
  1. IL_StatusProcesso 
  1. IL_Empresa 
  1. IL_AtividadeManualExecutada 

1.2 As cargas de PR a serem configuradas são: 
  1. PR_RespostaAtividadeManual 
  1. PR_TipoSolicitacaoOrquestrada 
  1. PR_DefinicaoTarefa 
  1. PR_TarefaExecutada 
  1. PR_Fluxo 
  1. PR_TipoAtividadeManual 
  1. PR_StatusTarefa 
  1. PR_Milestone 
  1. PR_TipoMetodoTarefa 
  1. PR_API 
  1. PR_Metodo 
  1. PR_Usuario 
  1. PR_Processo 
  1. PR_StatusProcesso 
  2. PR_Empresa 
  1. PR_AtividadeManualExecutada 

2. Clique em New Job e inicie uma nova Job. Nesta etapa, será configurado as Job's da IL.


 

 













3. Adicione um nome para a Job e em seguida clique em Steps. 

 


















4. Em Steps, clique em New para iniciar a configuração das cargas. 

 

 















5. Crie 16 steps de acordo com a lista de cargas mencionadas no passo 1 (Neste item serão as cargas de IL). Adicione o nome da stop de acordo com a carga a ser criada, em type selecione SQL Integration Services Package e em Server coloque o IP do servidor de banco de dados de onde os dados serão extraídos. 












 





 6.Clique em package, localize a carga que esteja a configuração e clique em ok. 

​


  













7. Clique em configuration, selecione o Environment configurado e clique em Ok. 


 















8. Agora clique em New novamente para criar as steps para o restante das cargas repetindo os mesmos passos acima. 



 














9. Após cadastrar todas as step’s, elas ficarão numeradas na Job de acordo com a imagem abaixo. 




 














10. Após cadastrar todas as step’s, configure-as para avançar para a próxima em caso de sucesso ou falha, para não ter nenhuma parada nos serviços. Para isso, clique na primeira step e clique em Edit. 

 
















11. Clique em Advanced e configure para ir para próxima step em caso de sucesso e em caso de falha. Para isso, selecione a próxima de acordo com a figura abaixo e repita isso para as próximas steps. 
  

















12. Detalhe importante: Antes de iniciar a Job de IL, precisamos iniciar o pacote EnableCDC. Acesse o Integration Services e procure pelo EnableCDC, clique com o botão direto do mouse e depois em execute. Selecione o Environment, clique em ok que ele irá executar logo em seguida. 
 
 

 













13. Em caso de sucesso do EnableCDC, inicie a Job de IL de acordo com a imagem abaixo e aguarde o término. Dependendo do tamanho de banco de dados, pode demorar bastante. 

 

 


Configuração de uma nova Job  

Com o sucesso de todas as step’s da Job de IL, configure uma nova Job com todas as cargas de PR (lista de cargas do item 1.2, na etapa anterior) que precisa ser iniciada diariamente através de um agendador de tarefas no SQL.  
Para configurar a step de PR, siga os mesmos passos usados na IL, no item 2 da etapa anterior.  

  1. Altere o name e package para PR.  
 

 

  1.  Em Schedules, crie um agendamento de diário desta step de PR. 

 















  1. Em name, coloque o nome da tarefa. Em Frequency, marque-a como diária e selecione um melhor horário para execução. 

 

 















  1. Clique em ok.