SSIS – Autenticando servidor SMTP para envio de e-mail

Quando falamos sobre SQL Server Integration Services (SSIS) muitos desenvolvedores já associam o mesmo à ETL (Extract, Transform e Load), ou seja, extrair dados de uma origem para um destino, o que não deixa de ser verdade. Mas a utilização do SQL Server Integration Services vai muito além de extração de dados, pois ele é uma plataforma para integração de dados em nível corporativo e soluções de transformações de dados já que existem muitos recursos nessa plataforma que poderão nos ajudar no dia a dia.

O SSIS foi introduzido a partir do SQL Server 2005, mas vale ressaltar que nas versões anteriores também existia uma ferramenta que nos auxiliava no processo de ETL, que utilizava uma forma menos visual, mas bastante objetiva: o Data Transformation Services (DTS).

Vale ressaltar que o Data Transformation Services é uma ferramenta e não uma plataforma como o SSIS, sendo assim, sua utilização para extração de dados (exportação/importação) é bem simples se comparado aos recursos disponíveis no SSIS.

Neste artigo veremos como é fácil trabalhar com essa plataforma, onde, juntamente com o .NET Framework e linguagens como Visual Basic e Visual C#, possibilita muitas alternativas para resolver problemas do dia a dia.

Cenário

O cenário para esse artigo baseia-se na criação de um pacote SSIS onde o foco está na configuração de uma conta autenticada para envio de e-mail. Embora exista um task para configurar um servidor de e-mail, o mesmo não possui opções avançadas disponíveis para os parâmetros, como porta, servidor de e-mail, entre outras configurações. Para esse cenário será utilizado o servidor SMTP do GMAIL.

A criação dos pacotes SSIS não simplesmente monta o fluxo, mas algumas informações como política de segurança, regras para envio de dados a clientes devem ser levados em consideração, pois o dado é o bem mais valioso de uma empresa e é o dever de cada um zelar por eles. Essa é a opção mais adequada em se tratando de extração/importação de dados, possibilitando assim criação de padrões e centralização de regras de negócios.

Vamos ao cenário: foi solicitada a extração de um relatório diariamente, onde o mesmo deverá ser enviado por e-mail para algumas pessoas e, como a empresa não possui um servidor de e-mail próprio, existe a necessidade de utilizar uma conta do GMAIL. Dessa forma, não será possível a utilizar uma conta anônima, pois a utilização desse servidor de e-mail requer a indicação da porta do servidor SMTP, o nome do servidor, conta e usuário de autenticação. Após o levantamento de requisitos, pode-se imaginar um cenário muito simples, mas no decorrer de seu desenvolvimento o mesmo mostrará um nível intermediário.

Esse processo de automatização irá contribuir muito para o dia a dia do DBA/Desenvolvedor, pois poderá utilizar o tempo de geração do relatório para outras tarefas, uma vez que essa extração será gerenciada pelo Job do SQL Server Agent sem nenhuma intervenção.

Para esse artigo, o ambiente utilizado será o SQL Server 2012 Enterprise 64 bits e o SQL Server Data Tools for Visual Studio 2012, conforme mostra a Figura 1.

Figura 1. Informações SQL Server Data Tools

Esse artigo poderá ser desenvolvimento a partir de qualquer versão do SQL Server, a partir da Standard 2005.

Para saber qual a versão do SQL Server está disponível, podemos utilizar o comando a seguir:

 select @@VERSION 

O resultado indicará algo como os dados a seguir:

Microsoft SQL Server 2012 - 11.0.5343.0 (X64) May  4 2015 19:11:32 Copyright (c) Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 6.3 <X64> (Build 9600).

Além disso, o desenvolvimento do pacote poderá ser feito a partir da versão Business Intelligence Development Studio (BIDS) do SQL Server 2005.

Para a extração dos dados será utilizado o database AdventureWorks2012, mas para esse artigo qualquer base que se tenha domínio por ser utilizada.

Solução

O SSIS conta com tarefas (tasks), que são elementos de fluxo de controle que definem unidades de trabalho que são realizadas em um pacote de fluxo de controle. Este é composto por uma ou mais tasks e conectadas pelo fluxo de controle.

Para o envio de e-mail, na maioria dos casos, o uso da task Send Mail (Figura 2) seria o suficiente, pois a empresa possui seu próprio servidor de e-mail, onde uma autenticação anônima já resolve todos os problemas, como podemos ver na Figura 3 os poucos parâmetros necessários.

Porém, como estamos usando um servidor SMTP externo, isso exigirá configurações mais avançadas.

Figura 2. Task Send Mail – Control Flow

Figura 3. Parâmetros SMTP como anônimo

Para configurar o SMTP Connection, deveremos informar os parâmetros conforme a Tabela 1.

Tabela 1. Parâmetros SMTP Connection

Em se tratando do task Send Mail, não existe problemas em sua configuração. Agora quando o assunto é a configuração de uma conta SMTP externa, o procedimento adotado será bem diferente.

A configuração do servidor SMTP externo com a autenticação de usuário deve, antes de mais nada, levantar todas as informações referente a esse servidor, como: usuário, senha, servidor SMTP e porta. E de posse dessas informações, a configuração poderá ser realizada para o início dos testes.

O primeiro passo será a criação das variáveis que serão utilizadas para a passagem dos parâmetros do servidor SMTP. Procure criar variáveis com nomes próximos a cada parâmetro que for utilizar, para facilitar sua identificação no caso de algum erro e ajudar no desenvolvimento de quaisquer rotinas.

Os nomes sugeridos para este artigo estão na Figura 4.

Figura 4. Variáveis para envio de e-mail

Na Tabela 2 está o detalhamento de cada variável e a utilização de cada uma delas.

Tabela 2. Detalhe de cada variável

Vale ressaltar que não há necessidade de criar as variáveis nessa ordem.

Após as definições das variáveis será necessário codificar cada uma para que se possa autenticar no servidor SMTP e enviar um e-mail com anexo.

A task utilizada para a codificação é a Script Task, da Figura 5.

Figura 5. Script Task localizado em Control Flow Items

Essa task possibilitará escrever códigos para qualquer necessidade, bastando para isso definir qual linguagem utilizar para codificar e também quais variáveis utilizar nesse processo.

Na Figura 6 pode-se verificar várias propriedades e, dentre elas, a definição da linguagem de script que iremos utilizar, que poderá ser o Microsoft Visual Basic 2012 ou o Microsoft Visual C# 2012.

Dessa forma, será necessário definir qual a linguagem de script e qual tipo de variável que será utilizada, ou seja, ReadOnlyVariables ou ReadWriteVariables.

Para o artigo o tipo de variável será o ReadOnlyVariables, pois temos como propósito somente ler os valores e não recebe-los. No caso de existir a necessidade de leitura/escrita, a escolha mais apropriada será a ReadWriteVariables.

Na Figura 7 pode-se verificar que todas as variáveis foram indicadas como ReadOnlyVariables.

Figura 6. Tela de definições do Script Task Editor

Figura 7. Tela com os parâmetros definidos

Após as definições do tipo de variável e linguagem, o passo seguinte será escrever o código com a script language definida. Após o click no botão Edit Script, uma nova interface como a da Figura 8 será aberta para a codificação do projeto.

Figura 8. Interface para codificação

Como será necessário o envio de e-mail, é essencial importar a classe System.Net.Mail, pois sem essa classe o processo não ocorrerá, retornando um erro de que a classe não foi importada. A mensagem de erro será transparente para o DBA/Desenvolvedor.

O processo de importação da classe responsável pelo e-mail ou qualquer outra classe no Visual C# é simples, basta utilizar o código:

using <Classe>; 

A codificação referente ao procedimento de envio de e-mail com arquivo em anexo está na Listagem 1. O código está comentado nos principais pontos, facilitando o entendimento.

Listagem 1. Código envio e-mail com arquivo em anexo – EnvioArq

      01. public void Main()
      02. {
      03.   // TODO: Add your code here
      04.
      05.  //Informacao do Subject(Assunto) e Body(Corpo mensagem) do e-mail
      06.  string vSubject = Dts.Variables["vSubjMail"].Value.ToString();
      07.  string vBody = Dts.Variables["vBodyMail"].Value.ToString();
      08.
      09.   //Indica a prioridade de envio do e-mail(1-High / 2-Normal / 3-Low)
      10.  int vPriority = 2;
      11.   if (SendMail(vSubject, vBody, vPriority))
      12.  {
      13.       Dts.TaskResult = (int)ScriptResults.Success;
      14.   }
      15.   else
      16.   {
      17.     Dts.TaskResult = (int)ScriptResults.Failure;
      18.   }
      19. }
      20. public bool SendMail(string pSubject, string pBody, int pPriority)
      21. {
      22.     try
      23.            {
      24.                string vEmailServer = Dts.Variables["vServerMail"].Value.ToString();
      25.                 string vEmailUser = Dts.Variables["vUserMail"].Value.ToString();
      26.                string vEmailPass = Dts.Variables["vPassMail"].Value.ToString();
      27.                string vEmailPort = Dts.Variables["vPortaMail"].Value.ToString();
      28.                string vEmailFrom = Dts.Variables["vFromMail"].Value.ToString();
      29.                string vEmailFromName = "DevMedia-SSIS";
      30.
      31.                string vEmailSendTo = Dts.Variables["vToMail"].Value.ToString();
      32.
      33.                string vEmailAttach = Dts.Variables["vAttachMail"].Value.ToString();
      34. 
      35.                SmtpClient smtpClient = new SmtpClient();
      36.                MailMessage message = new MailMessage();
      37.                 MailAddress fromAddress = new MailAddress(vEmailFrom, vEmailFromName);
      38.                int vEmailSrvSMTP = int.Parse(vEmailPort);
      39.
      40.                smtpClient.Host = vEmailServer;
      41.
      42.                smtpClient.Port = vEmailSrvSMTP;
      43.                smtpClient.EnableSsl = true;
      44.                 System.Net.NetworkCredential myCred = new System.Net.NetworkCredential(vEmailUser, vEmailPass);
      45.                smtpClient.Credentials = myCred;
      46.
      47.                message.From = fromAddress;
      48.
      49.                 message.To.Add(vEmailSendTo);
      50.
      51.                 switch (pPriority)
      52.                {
      53                    case 1:
      54.                        message.Priority = MailPriority.High;
      55.                        break;
      56.                    case 3:
      57.                        message.Priority = MailPriority.Low;
      58.                        break;
      59.                    default:
      60.                        message.Priority = MailPriority.Normal;
      61.                        break;
      62.                }
      63.
      64.                if (vEmailAttach != null && vEmailAttach != "")
      65.                {
      66.                    Attachment myAttach = new Attachment(vEmailAttach);
      67.                    message.Attachments.Add(myAttach);
      68.                }
      69.
      70.                message.Subject = pSubject;
      71.                message.Body = pBody;
      72.                smtpClient.Send(message);
      73.                message.Dispose();
      74.                return true;
      75.            }
      76.            catch (Exception ex)
      77.            {
      78.                return false;
      79.            }
      80.       }

A codificação para o envio de e-mail é simples e funcional e os recursos apresentados são os necessários para esse artigo e nada impede de incluir outros recursos nesse código.

Na linha 12 temos a função responsável por enviar o e-mail. Se for OK retorna sucesso para o pacote, caso contrário irá retornar um erro.

Já na linha 20 temos a função com parâmetros para o envio de e-mail, onde são passados três parâmetros: pSubject, pBody e pPriority.

A partir da linha 24 temos as variáveis utilizadas para o processo de envio de e-mail: das linhas 24 a 29 temos as variáveis comuns.

A partir da linha 31 temos as variáveis para envio com sucesso. Caso seja necessário algum endereço em cópia devemos usar o seguinte comando:

string vEmailSendCC = Dts.Variables["vEmailSndCC"].Value.ToString();

Repare que a linha 35 inicializa uma nova instância do smtpClient e na linha 36 temos a inicialização de uma nova mensagem de e-mail.Já a linha 37 merece um destaque, pois ela representa o endereço de e-mail do remetente, passado as variáveis com o endereço de e-mail. Na linha 38 temos a conversão da variável indicando a porta do servidor para integer. As linhas 39, 40 e 41 indicam, respectivamente, o servidor de e-mail, a porta do servidor e a habilitação da Conexão Criptografada. A linha 42 inicializa uma nova instância de Credencial, onde é passado como parâmetro o usuário e senha de autenticação.

Algumas indicações são extremamente importantes para que a task não apresente erro:

• A linha 47 indica a mensagem ao e-mail do remetente;
• A linha 48 indica a mensagem ao e-mail dos destinatários.

O switch da linha 49 verifica qual a prioridade da mensagem passada por parâmetro.

A partir da linha 64 temos o tratamento para quando queremos anexar arquivos a mensagem, com as seguintes indicações:

• A linha 70 indica o Assunto do E-mail;
• A linha 71 indica o corpo do E-mail;
• A linha 72 tem o objeto responsável por enviar o e-mail com todos os dados necessários e;
• A linha 73 finaliza a conexão TCP como o Host, liberando todos os recursos. Ao final, se não der nenhum erro, retorna com sucesso.

A Figura 9 mostra o workflow completo do pacote SSIS desenvolvido, que é composto do Data Flow Task, Execute Process Task e Script Task.

Figura 9. Workflow final com as Script Task

A seguir vamos entender melhor as tasks geradas:

1. GerarArquivo(Data Flow Task): Responsável por importar os dados de uma tabela para um arquivo com extensão CSV. Dentro desse processo estamos trabalhando com outras duas tasks, conforme mostra a Figura 10. Nesse processo os dados serão importados da tabela (OrigemDados) para um arquivo com extensão CSV (DestinoDados).
   
   Figura 10. Tasks para importação de dados e geração de arquivo
   
   • OrigemDados(OLEDB Source): Trata-se da origem dos dados, nesse caso estamos trabalhando com dados de uma tabela, mas pode ser também um comando T-SQL, Variáveis, Views.
   • DestinoDados(Flat File Destination): Trata-se do destino dos dados, nesse caso estamos trabalhando com arquivos CSV.
2. CompactarArquivo(Execute Process Task): Responsável por compactar o arquivo gerado na task GerarArquivo. No nosso caso, o compactador utilizado é o winRar, assim, evita-se que o e-mail não seja enviado devido ao tamanho do arquivo. Na Figura 11 temos todos os parâmetros configurados para compactar o arquivo.
   
   Figura 11. Propriedades Execute Process Task
   Os principais parâmetros utilizados nessa task são os mesmos apresentados na Tabela 4.
   

   Parâmetros	Comentários
   Executable	Indicar o caminho completo do compactador
   Arguments	Os parâmetros do compactador e o caminho completo do(s) arquivo(s) a ser(em) compactado(s).
   WorkingDirectory	O diretório de trabalho, ou seja, onde está(ão) o(s) arquivo(s)
   WindowStyle	Hidden - não mostra para o usuário a tela compactando o(s) arquivo(s).

   
   Tabela 4. Parâmetros da Process task
3. EnviarMail(Script Task): para o entendimento desse task observe a Figura 5.

Ao finalizar o pacote SSIS, pode-se realizar os devidos testes e verificar se o e-mail foi enviado com sucesso ou não.

Na Figura 12 pode-se verificar a evidência de que o pacote SSIS foi executado com sucesso e com o arquivo em anexo. Mesmo compactado, o arquivo está com um tamanho de 3 MB.

Figura 12. E-mail recebido

Esse artigo abre a possibilidade para outras necessidades onde o envio de e-mail e geração de arquivos seja preciso.

Features administrativas, ou seja, funções importantes para o dia a dia de um DBA também poderão ser criadas e monitoradas através de e-mail.

Pode-se perceber que os procedimentos apresentados nesse artigo foram executados sem colocar em risco o database e os dados.

O procedimento de envio de e-mail apresentado nesse artigo atenderá a todas as situações onde é necessária a autenticação num servidor SMTP. As configurações do servidor SMTP utilizadas nesse artigo são de conhecimento de todos os usuários cadastrados e está disponível no próprio site do Gmail.

Esse artigo demonstra que a plataforma SSIS é poderosa e possibilita o desenvolvimento de muitas soluções para o dia a dia, seja na parte administrativa de uso exclusivo de um DBA, ou para procedimentos de extração de dados, para o uso de tomadas de decisões, onde os dados poderão ser de diversas fontes de origem e gerados para diversas fontes de destino.

Esperam que tenham gostado e até a próxima.