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.

Figura 1.

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 Drop”, 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 multiplas conexões
Integração com banco de dados, além do SQL Server
Permite facilmente a implementação de T-SQL
Altamente customizável
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:

Figura 2.

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.

Figura 3.

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 credenciais para acessar o SQL Server ou o banco dados utilizado, com sucesso.

Figura 4.

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! :D, agora ele mostra para você, com méritos de conferência, se está realmente é a connection string esperada.

E Finish caso seja.

Figura 5.

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.

Figura 6.

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, 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:

Tenha uma variável a nível de Pacote para receber o retorno
No Execute SQL Editor, defina em Result Set o valor Single Row
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 distribuido em várias variaveis 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 atribuia o tipo de dado Object
	2. O Result name deve ser obrigatóriamente 0 (Zero)
	· Utiliza-se este em caso de necessidade de utilizar um loop na estrutura

Veja como deve ficar sua POC.

Figura 7.

Agora a opção Result Set:

Figura 8.

Veja minha Procedure:

Figura 9. Listagem

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:

Figura 10. Para Sucesso.

Figura 11. Em processo.

Figura 12. 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:

Figura 13. Listagem

Agora veja o SSIS:”

Figura 14.

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.

Figura 15.

Este é o nosso resultado:

Figura 16.

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

Figura 17. Control Flow

Data Flow

Figura 18. Data Flow

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

Figura 19.

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:

Figura 20.

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:

Figura 21.

Veja o resultado do fluxo dentro do Data Flow.

Figura 22.

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.

Figura 23.

Figura 24.

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:

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.
Procure a opção expression no canto esquerdo da tela.
Clique na Reticência.
Na nova janela, em Property, procure por conectionstring.
Em Expression clique nas resticencias, para abrir uma nova janela.
Expression Builder, defina quais variaveis 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 Variaveis devem seguir os valores:

Diretorio: B:\Sala
NomeArquivo: Moveis.txt
Concatenado: B:\Sala\Moveis.txt

Figura 25.

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

Banco de Dados - SQL Server