Como enviar emails desde SSIS con el SMTP de Gmail

Para cualquier proceso de carga o integración de datos, u otras tareas que se desarrollen con SQL Server Integration Services, SSIS para los amigos, es de gran utilidad disponer de un servicio que permita que la ejecución de la tarea pueda informar de que ha terminado correctamente, de que ha fallado, o que pueda enviar, por ejemplo, un informe o un log con los resultados de la ejecución.

Para ello tenemos la task de envío de emails 'Tarea enviar correo', que con sólo informar los datos de un servidor SMTP que deje al proceso enviar sus emails, y los datos que queremos informar en asunto, cuerpo, etc. del email ya nos permite enviar un email a quien queramos en cualquier punto de la ejecución de nuestros paquetes.

Si tenemos un servidor SMTP en nuestra red que permita a SSIS utilizarlo para este propósito sin necesidad de autenticación todo resuelto, pero si no es así la cosa se complica un poco.

El problema principal es que la tarea de SSIS no permite informar datos de autenticación contra el servidor SMTP, y si el servidor requiere autenticación para su utilización, que es lo normal, nuestra task fallará.

Estos días me he encontrado justo con este problema, y como he probado bastantes cosas antes de poder enviar por fin un email desde SSIS, voy a recoger aquí un resumen de mis peripecias para conseguirlo, incluyendo lo que no me ha funcionado.

Soluciones para enviar emails desde SSIS con SMTP de Gmail

Las soluciones que he ido probando se han basado en las que se proponen en este blog de SSIS. Enlazo el primer post, que explica cómo utilizar la 'Send Mail Task', siempre que no haya que autenticarse :)

Aclarar que mi objetivo era poder enviar emails desde SSIS sin disponer de IP fija ni un dominio propio asociado al servidor de SQL Server o a mi red. Para ello, al final siempre tengo que ir a utilizar un servicio de SMTP de terceros, en este caso Gmail.

Mi servidor es un Windows Server 2008 R2, y la base de datos un SQL Server 2012 con Integration Services y Microsoft Visual Studio 2010.

Estas son las opciones que he probado, primero las enumero, y después explico en detalle cada una:

1. Crear en el servidor de SSIS un servicio de SMTP que a su vez utilice el servicio SMTP de Gmail
    
2. Utilizar un script de VB o C# para poder informar los parámetros de autenticación y poder enviar emails
    
3. Utilizar tareas implementadas por terceros, concretamente la Send Mail Task Plus de la librería de Cozyroc
    
4. Crear en la base de datos SQL Server un perfil y cuentas de correo electrónico para después poder enviar emails llamando a un procedimiento almacenado.

Crear en el servidor de SSIS un servicio de SMTP que utilice el servicio SMTP de Gmail

Siguiendo la primera parte de las indicaciones de la segunda solución del blog de SSIS que mencionaba antes, muy bien explicadas en castellano en este post de El Bruno, he creado en mi servidor Windows Server 2008 R2 un servicio SMTP que a su vez utiliza para transmitir los emails el servicio SMTP de retransmisión de Gmail.

Después de seguir todos los pasos, agregando las características del servicio SMPT para mi server, y configurando después el servicio desde el entorno de Internet Integration Services (IIS) para que reenvíe los mails a través del SMTP de Gmail, he conseguido que la tarea de IS se ejecute correctamente, sin devolver ningún error, pero los emails de prueba que he enviado no llegaban a su destino.

Lo curioso es que al no conseguir enviar los emails, pasé a explorar otras opciones, y al día siguiente, justo cuando estaba haciendo el test de la opción de enviar emails directamete desde la base de datos, me llegaron los emails de prueba de la tarea de SSIS que el día anterior me daba envío OK. Lo malo es que al volver a intentar ejecutar la tarea de SSIS, ésta fallaba diciendo que el usuario de Gmail que utilizo para la autenticación no tenía permisos para retransmitir.

Supongo que en las últimas pruebas desconfiguré alguna cosa, y no he tenido más paciencia para encontrar lo que era, he preferido explorar las otras soluciones, pero si alguien me quiere iluminar al respecto se lo agradeceré.

La duda más importante que me ha quedado de esta configuración es que en la guía que estaba siguiendo, donde se informa el nombre del servidor, el autor informaba un nombre de dominio completo, con server.dominio.com, y yo sólo informaba el nombre de mi servidor local, 'miserver', sin dominio. Probé por si acaso a cambiar este nombre por mail.dataprix.com, o incluso miserver.dataprix.com, pero tampoco funciónó.

Utilizar un script de VB o C# para poder informar los parámetros de autenticación

Como la prueba de utilizar la misma tarea después de configurar el servidor de SMPT en mi server no había salido muy bien, volví a mi fuente de inspiración, la segunda solución del blog de SSIS, que justo propone configurar este servicio para después solucionar la tarea de envío de emails con la Task de Script.

Dicen que lo que no hacen las tareas de SSIS siempre se puede hacer con programación, así que una buena solución es utilizar la tarea de Script para enviar los emails con código VB o C#.

Hice una prueba rápida con el código que publica el mismo blog, y no me funcionó, me devolvían una excepción.

La excepción salta de esta manera porque el script no tiene control de excepciones (try/catch), pero debugando el código o incluyendo el control de excepciones se puede consultar el error que provoca la excepción, que puede ser simplemente una autenticación fallida, un puerto incorrecto o que no esté activado el envío por SSL.

Después de hacer algunas pruebas conseguí crear un script sencillo que sí hacía el envío de emails sin errores ni excepciones. Este es el código supersimplificado que a mi me funciona bien. Si alguien lo reutiliza, al menos que incluya el control de excepciones:

' Microsoft SQL Server Integration Services Script Task
' Write scripts using Microsoft Visual Basic 2008/2010.
' The ScriptMain is the entry point class of the script.

Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime

Imports System.Net
Imports System.Net.Mail
Imports System.Data.SqlClient
Imports System.Xml

<Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute()> _
<System.CLSCompliantAttribute(False)> _
Partial Public Class ScriptMain
    Inherits Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase

    Enum ScriptResults
        Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success
        Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
    End Enum

    Public Sub Main()

        Dim myMessage As MailMessage
        Dim mySmtpClient As SmtpClient
        Dim myException As String
        Dim CCAddressList As MailAddress

        myMessage = New MailMessage("mimailde@gmail.com", "maildestino@gmail.com", "Mi asunto", "Prueba de envio de email")
        CCAddressList = New MailAddress("mailcopia@dataprix.com")
        myMessage.CC.Add(CCAddressList)
        mySmtpClient = New SmtpClient("smtp.gmail.com")
        mySmtpClient.EnableSsl = True
        mySmtpClient.Credentials = New NetworkCredential("mimailde@gmail.com", "mipassword")
        mySmtpClient.Send(myMessage)

        Dts.TaskResult = ScriptResults.Success
    End Sub

End Class

Utilizar tareas implementadas por terceros

Si el problema principal es la simplicidad de la Task que viene por defecto con Integration Services, una buena solución es utilizar una versión mejorada de esta tarea, concretamente la que incluye esta librería de Cozyroc.

Esta opción ha sido la más sencilla, sólo he tenido que descargar el instalador, y en 10 minutos tenía en mi barra de herramientas una nueva Task para enviar emails con propiedades para poder informar datos de autenticación, y un asistente para crear una conexión con el SMTP de Gmail. Una vez informados todos los datos, la tarea envió correctamente los emails de prueba, incluyendo ficheros adjuntos.

La única pega es que estas librerías no son gratuitas, se pueden utilizar sin problemas desde el entorno gráfico de Integration Services, pero si no se adquiere la licencia las tareas no funcionan cuando se las llama desde un job.

Para lo que yo quiero hacer no me compensa pagar la licencia, pero me lo apunto porque realmente estas librerias aportan mucha más potencia al entorno de SSIS. Incluyen tareas que mejoran muchas de las existentes, y nuevas tareas para hacer cosas como controlar procesos CDE con Insert/Update/Delete, conectar con Microsoft AX Dynamics, CRM, Sugar CRM, Salesforce o incluso para generar ficheros de Tableau.

En pantalla, las propiedades de la versión extendida para envío de email, y mi cuadro de herramientas de SSIS después de importar la librería, con unas cuantas tareas más:

En el post también se menciona la Send HTML Mail Task, que se puede descargar de CodePlex. La enlazo porque seguro que también funciona bien, pero como no he encontrado con qué versión de SSIS funcionaba, no he llegado a probarla, si alguien lo hace, se agradece si nos explica qué tal funciona, y con qué versión/es funciona.

Crear en la base de datos SQL Server un perfil y cuentas de correo electrónico para después poder enviar emails llamando a un procedimiento almacenado.

Por fin, la última opción era crear directamente desde SQL Server con Management Studio un perfil y cuentas para enviar emails desde la base de datos, y llamar al procedure de la base de datos 'sp_send_dbmail' para enviar los mails.

La llamada al procedure se puede hacer tanto desde la misma base de datos, como desde una tarea de ejecución SQL de SSIS, por lo que la misma solución puede servir para más cosas.

Esta solución me ha funcionado bien, y sin mucho esfuerzo. Tiene la importante ventaja de que el entorno de SSMS permite probar fácilmente si los datos del servidor de mail se han introducido correctamente enviando un email de prueba, y en unos minutos tenía bien configurados los parámetros para utilizar el servidor SMTP de Gmail.

Aquí está explicado paso por paso. Parecen muchos, pero se configura bastante rápido, funciona bien, y sin dependencias de terceros. En el post Enviar correo desde SQL Server del blog de Augusto Humire también está muy bien explicado, y en castellano.

Después, llamar al procedure para enviar un email con un archivo adjunto es algo tan fácil como ésto:

EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'SSISmail perfil',
    @recipients = 'carlosss@gmail.com',
    @subject = 'Asunto de prueba',
    @body = 'hola, estamos probando',
    @body_format = 'HTML' ,
    @file_attachments = 'E:\Pruebas\default.xls';

Enlazo la página de documentación de msdn del procedure sp_send_dbmail, en la que se puede consultar todos los parámetros que se pueden utilizar y para qué sirve cada uno.

Si lo hacemos desde SSIS, en la tarea de ejecución de SQL utilizaremos esta misma llamada al procedure, y si queremos algo más flexible utilizaremos variables para informar los valores que tienen que recibir los parámetros del procedure, con lo que la sentencia en la tarea podría quedar de esta manera si, por ejemplo, utilizamos 4 variables:

EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'SSISmail perfil',
    @recipients = ?,
    @subject = ?,
    @body = ?,
    @body_format = 'HTML' ,
    @file_attachments = ?;

Definitivamente, yo me quedo con esta solución, ¿y vosotros?