Robson Alves O que torna um sonho irrealizável é a inércia de quem sonha

SQL Server Integration Service

 

Neste artigo irei mostrar as vantagens de se utilizar esta solução disponibilizada pela microsoft, qual é um avanço bem sucedido do antigo DTS que acompanhou o SQL Server 2000. No artigo tratarei o SQL Server Integration Service como SSIS.

 

O que é SSIS?

Uma plataforma que possibilita a integração dos diversos dados de banco de dados e o mais importante os dados, gerenciamento a múltiplas conexões, geração de arquivos de destino ou como fonte de dados primária, encaminhamento de e-mails, acesso FTP para envio de arquivos ou requisição de arquivos, permite o envio para vários destinos, por exemplo, uma replicação de dados em um banco de dados backup.

O SSIS Abre um mundo cheio de possibilidades para soluções empresarias complexas.

Tudo e mais um pouco que foi dito acima, são nada mais que componentes presentes na “ToolBox” do Visual Studio Businnes Inteligence 2005.

Não só isso, na versão 2005 do SSIS permite que seja acrescentado linhas de códigos utilizando os Namespace Microsoft.SqlServer.Dts.Runtime, System.Data. Para tal utilize o componente Script Task.

Com esta funcionalidade o SSIS ganha outras proporções, abrangendo e fortalecendo a estrutura criada para o negócio solicitante, salientando apenas que na versão 2005 o script é limitado apenas na linguagem VB.Net, já na versão 2008 do BI do Sql Server 2008, esta função permite códigos na linguagem C# 3.5.

Abaixo darei maiores detalhes da sua utilização.

Aonde Implementar esta solução

O grande ganho da utilização desta plataforma, está descrito no seu próprio nome, integração. Em casos de negócio onde exige uma alta integração de dados entre sistemas legados, você tem duas escolhas logo de cara, fazer um agente que irá trabalhar entre os dois extremos, realizando a validação do dados, gerenciamento das conexões, tratamento de erros, conversão de tipos de dados, exportação de arquivos caso seja o negócio ou a inserção de todos os dados em mais de uma fonte de dados, ou seja, um cenário que pode ter diversos fins e uma enorme alocação de recurso, ou utilizar ferramentas de mercado reconhecida, testada, documentada, facilitando o trabalho dentro desta gama de problemas apresentados e os quais são resolvidos com simples “Drag and. Drope”, sem exigir em um caso complexo de negócio, grandes conhecimentos técnicos, apenas boa prática com os componentes do SSIS.

Vantagens do SSIS

Elencando:

· Usabilidade intuitiva

· Facilidade no gerenciamento de múltiplas conexões

· Integração com banco de dados, além do SQL Server

· Permite facilmente a implementação de T-SQL

· Altamente customizavel.

· Controle total sobre o fluxo definido

· Auditoria das transformações dos dados.

Desvantagens do SSIS

Elencando:

· Incapacidade de rodar em paralelo, seus processos são sub-sequentes.

· RollBack, não existe nenhuma ferramenta nativa para tratar deste caso

· Manutenção torna-se difíceis em pacotes de alta complexidade

· Baixa Escalabilidade, devido a sua hierarquia, torna difícil escalar o processamento em casos de gargalho com Stored Procedures

Prova de Conceito

Passado esta breve do SSIS, vamos a prova de conceito trazendo algumas da experiências com a ferramenta.

Criando um Pacote

Um pacote, assemelha-se a uma solution class library, onde acopla todas as soluções de classes, neste caso o pacote (Class Library) acopla todos os fluxo que o SSIS fará para a integração de sistema.

Para gerar o um pacote, abra o:

Em seguida clique “New Project” e escolha o template do “Integration Services Project”, Defina o Nome que preferir e pronto, seu projeto deverá ficar desta forma.

Veja que as pastas são intuitivas e organizadas automaticamente. Vamos então gerar nossa Primeira fonte de dados.

Definindo uma Fonte de Dados

Clique com o direito em Data Sources e “New Data Source”.

O wizard pedirá algumas informações e credeciais para acessar o sql server ou o banco dados utilizado, com sucesso.

Intuitivo, você pode criar uma nova fonte, nos moldes do Visual Studio DataBase Connection ou informar uma connection string, nos parâmetros utilizadas no Web.Config.

Em seguida Next! , agora ele mostra para você, com méritos de conferência, se está realmente é a connection string esperada.

E Finish caso seja.

Como citado nos pontos positivos desta plataforma, é possível criar diversos outros data sources, tantos quanto necessário para o seu negócio.

Criando um Fluxo de Dados

Antes de entrarmos na geração do nosso fluxo, vamos entender como funciona as janelas e abas de trabalho de um pacote.

 

Control Flow: Defini o fluxo macro do seu negócio, aqui você inserir Comandos via script, T-SQL, Conexões FTP, E-Mail, tarefas via arquivo XML, e o mais utilizado Data Flow Task.

Este fluxo utilizará a segunda aba, nela é possível definir fonte de dados origem, fonte de dados destino, condições, tratativas, conversões, adição de colunas, merge join, Record Set ( Onde é possível efetuar merge entre dois Record Set) entre outros.

Para este exemplo do Poc, vamos gerar uma movimentação de dados simples, apoiando-se nos conceitos supra citados e ver que com pouco esforço uma enorme estrutura.

Para este exemplo vamos executar uma procedure que retorna diversas informações quais usaremos para executar um processo sub-sequente e ver o resultado disto em um arquivo txt de preferência.

Comecemos com um Execute SQL Task, no bom modo Arraste e solte.

Clicando duas vezes sobre o objeto é possível em fim ir para as tarefas avançadas deste objeto, definindo então data sources, T-SQL, Error Output, nome da Label do objeto, tempo de timeout.

Em connection, escolha uma das fontes geradas de dados geradas, caso apareça a informação que deve ser gerado uma nova, clique sobre ela, a tela seguinte permitirá escolher a fonte de dados configurada anteriormente ou criar novas.

Ainda dentro do Execute SQL Task Editor em SQL Statement Defina o comando que deseja, a sintaxe é exatamente a mesma de uma transact SQL qualquer. No nosso caso iremos chamar uma procedure que irá retornar um valor. Portanto Exec NomeDaProc

Para que o SSIS receba o valor de retorno é necessário configurar algumas coisas:

1. Tenha uma variável a nível de Pacote para receber o retorno

2. No Execute SQL Editor, defina em Result Set o valor Single Row.

3. Na guia Result Set defina a variável que irá receber e o nome Result name deve ser igual o nome da coluna retornada na Procedure

Simplificando Result Set:

Aqui o SSIS saberá que terá que recuperar valores e onde armazenar, para entender os casos onde utilizar este resultset baseie-se na tabela abaixo:

Result Set	Descrição
None	Nenhum retorno interessa para este T-SQL
Single Row	Apenas a primeira linha das informações retornadas te interessa e neste caso você será capaz de recuperar campo a campo da linha distribuído em várias variáveis e não a linha inteira em uma variável
Full result set	Retorna uma coleção de dados, para que seja possível retornar essas informações corretamente, siga estes passos: 1. Crie uma variável a nível do Pacote e atribuía o tipo de dado Object 2. O Result name deve ser obrigatoriamente 0 (Zero) · Utiliza-se este em caso de necessidade de utilizar um loop na estrutura

Veja como deve ficar sua POC.

Agora a opção Result Set:

Veja minha Procedure:

O convert foi necessário pois o tipo de variável definido no SSIS foi Int32

O SSIS tem três status quando o pacote está em execução:

Para Sucesso.

Em processo

Error

Continuando, após definir a variável que recuperará um único valor da coluna da primeira linha do resultado, agora vamos passar esse valor para um Data Flow processar e jogar o resultado todo em um arquivo em pasta Local.

Portanto, definia um Data Flow Task,antes de prosseguir ligue o processo anterior ao data flow, arrastando a seta verde para cima do objeto.

Pronto dentro do Data Flow Task gere um Oledb source, defina suas conexões, em ‘Data acess mode” defina SQL Command, pois iremos atribuir uma procedure que necessita de uma parâmetro de entrada.

Veja a Proc:

Agora veja o SSIS:

Veja que ao clicar em Parameters... devemos definir de onde virá o valor do parâmetro de entrada, então escolha a variável utilizada anteriormente, Parameters defina com o mesmo nome da variável encontrada no SQL.

Ao clicar em Columns, você já poderá ver que o SSIS já possui um possível retorno da sua procedure, isso ele carrega no metadata sempre, sem ao menos você requisitar.

Você também é capaz de definir qual das colunas serão visíveis pelo fonte de dados destinos.

Feito isso vamos definir nosso Flat File Destination. Arraste, Solte e Ligue com o Data OLEDB Source.

De dois clique no objeto e defina uma nova conexão, neste caso entra alguns outros conceitos como o modelo do arquivo, qual o tipo de delimitador para que o SSIS faça quebra de linhas, se este arquivo será sobre-posto, se as colunas terão largura pré-definidas, de inicio escolha Fixed With.

Em File name defina um caminho Hard-Coded (este parâmetro pode ser dinâmico e vou mostrar a frente).

O SSIS já identifica pra você quais as colunas posteriormente serão inseridas no arquivo, você consegue delimitar, o arquivo conforme a necessidade. Ele também permite que você ignore uma quantidade X de linhas primárias de um retorno de query, neste caso não cabe está solução.

Feito as configurações, clique em ok para salvar a conexão.

Clique em Mappings, o SSIS já liga os pontos da sua fonte de dados com o arquivo de destino automaticamente.

Este é o nosso resultado:

Simples, porém o processo é complexo, temos ai horas de programação e tratamento de erros, pois quando se trata de arquivos de exportação, infinidades de casos podem ocorrer e mesmo uma boa equipe deixa passar alguns casos, trazendo re-trabalho e maior incidência de Programação Orientada a Gambiarra (POG).

Vamos ao resultado final do nosso fluxo:

Control Flow

Data Flow

Buscando o arquivo no diretório definido, veja o resultado:

Em poucos passos, diversas atividades são feitas rapidamente, lembrando isso ser apenas o mais básico de todo o SSIS.

Apenas complementando, segue dois componentes que geralmente são utilizados e tem uma importância enorme: Derived Column e o Data Conversion.

Para adicionar uma coluna nova em uma fonte de dados de origem, sem que isso implique em gerar uma coluna nova no banco de dados e nem tenha que duplicar os processos, utilizamos o Derived Column, segue um exemplo prático dentro do próprio POC.

Vamos passar o Id_processamento também para dentro do Arquivo. Para tal clique duas vezes no objeto, clique em variables e arreste a variável ou até mesmo outras colunas e defina as demais configurações:

Em seguida, vamos utilizar o Data Conversion, para converter um dado do retorno que está conflitando com o tipo de dado inserido dentro do arquivo.

Clique duas vezes no objeto, selecione a coluna desejada e defina para qual tipo de dado será copiado:

Veja o resultado do fluxo dentro do Data Flow.

Para que possa acrescentar a nova coluna no arquivo, você precisa resetar as colunas do arquivo e gerar novas colunas, ir em mapping em seguida, e informar o caminho de cada coluna.

 

Dica

Apenas para contribuir com um dica para a geração de arquivo, como em muitos casos, o nome do arquivo, caminho e tudo mais é gerado dinamicamente ou é armazenado em um banco de dados, mesmo o SSIS não sendo uma plataforma de run-time, é possível definir algumas expressões dentro do pacote e dos objetos de forma que antes de compilar totalmente os pacotes, o SSIS realize as operações destas expressões, então segue a dica:

1. Em data flow, na parte inferior da tela dentro de Connection manager, selecione a conexão gerada para o Flat file e clique em F4 ou com o direito em Properties.

2. Procure a opção expression no canto esquerdo da tela.

3. Clique na Reticências.

4. Na nova janela, em Property, procure por conectionstring.

5. Em Expression clique nas resticencias, para abrir uma nova janela.

6. Expression Builder, defina quais variáveis terão os valores que te interessa, neste caso as/a variável que possua o caminho e o nome do arquivo concatenados, caso seja duas variáveis faça assim : @[User::Diretorio] + "\\" + @[User::NomeArquivo]

As Variáveis devem seguir os valores:

Diretorio: B:\Sala

NomeArquivo: Moveis.txt

Concatenado: B:\Sala\Moveis.txt

Pronto, lá em File name não defina nada, o resto estará sendo encarregado por esta expressão criada.