Post original en JASoft.org: http://www.jasoft.org/Blog/post/SQL-Server-enviar-automaticamente-los-resultados-de-una-consulta-por-email.aspx

Hoy tuve que montar un sistema sencillo para poder enviar de manera periódica, en formato CSV, una serie de datos a unos usuarios. Los datos se obtienen a partir de una consulta más o menos compleja a una base de datos SQL Server.

Lo que necesitaba era que los resultados de esa consulta, una vez a la semana, se enviaran por email a un usuario para que pudiera abrirlos en una Excel y trabajar con ellos. Para conseguirlo no quería programar ni tampoco crear ningún paquete de integración específico ni hacer nada complicado. Lo único que necesitaba era definir la consulta, el formato y el destinatario. Punto.

Mala suerte: en SQL Server no hay nada incluido nativamente que nos permita conseguir algo así de manera tan directa, lo cual no deja de ser una pequeña vergüenza porque realmente es algo habitual y deberían facilitarlo un poco ¿verdad?

Así que tuve que improvisar y buscar una manera de conseguirlo sin complicarme demasiado la vida. A continuación os cuento el método que se me ocurrió y que creo que funciona muy bien. SQL Server no es mi especialidad por lo que puede que a alguien se le ocurra algo mejor. Si es así, por favor, déjame un comentario para compartirlo 🙂

Paso 1: Utilizar asistente de Importación Exportación para generar el archivo

SQL Server viene desde hace años con un asistente de importación y exportación de datos que es muy práctico y nos permite hacer cantidad de cosas. Gracias a este asistente definiremos la creación de un archivo de texto con los resultados de nuestra consulta. Luego ya veremos cómo enviarlo.

Abre el asistente desde el menú de inicio de tu equipo:

Al arrancarlo nos pedirá que indiquemos el origen de datos, en el cual elegiremos nuestro servidor SQL y la base de datos que nos interese.

En el segundo paso se nos pide que indiquemos un destino para importar los datos. En este caso vamos a elegir el driver para archivos planos, con los ajustes que vemos en la figura siguiente:

Le ponemos la extensión .csv al archivo. Es importante marcar la casilla de Unicode para no encontrarnos con que falla al haber caracteres no ASCII en algún campo. El formato más apropiado es el “Delimitado”, y en una pantalla posterior nos deja elegir exactamente cómo queremos delimitarlo. Si quieres (era mi caso) marca la casilla de abajo para que te incluya los nombres de los campos en la primera línea del archivo de texto.

Nota: Si tenemos instalado Office en el equipo podremos incluso elegir el formato Excel para el archivo resultante, pero este archivo CSV está siempre disponible y tal y como lo montaremos se abrirá en Excel directamente sin dificultad.

En un siguiente paso se nos pide la consulta a lanzar, y luego podremos configurar el archivo CSV:

Yo lo único que cambio es el delimitador de campos y le coloco un tabulador en lugar del valor por defecto. El motivo es que el tabulador (Tab) es el delimitador por defecto al importar en Excel y así le facilita el trabajo luego a los usuarios, que son sólo  hacer doble-clic sobre el archivo recibido lo abren como si fuera una hoja de cálculo normal.

En el último paso nos pregunta si queremos ejecutar la exportación inmediatamente y además si queremos guardarla como un paquete de integración, tanto a disco como directamente en SQL Server:

Yo, por ejemplo, lo guardo a disco para luego poder moverlo a otro servidor más fácilmente si quiero:

¡Listo!. Pulsamos “Finalizar” y se nos genera el paquete de integración que guarda nuestros resultados de consulta en un archivo .csv.

Paso 2: Configurar Database Mail

Antes de enviar por correo el CSV generado debemos configurar el correo electrónico de la base de datos para que nos permita enviarlo. Para ello abre el SQL Server Management Studio y vete al nodo de gestión. Encontrarás un nodo llamado “database mail”:

Haz doble-clic sobre él. Se abrirá un asistente para configurar el correo de la base de datos. Debemos definir un perfil de envío y un usuario para enviar:

El usuario define el remitente de los emails así como el servidor de envío que se utilizará. El perfil se usa para elegir uno o varios de esos usuarios para intentar el envío siguiendo la prioridad marcada.

Además de esto debemos asegurarnos de que el correo de la base de datos está habilitado, para lo cual deberemos ejecutar las siguientes instrucciones en una ventana de consultas:

   1: sp_CONFIGURE 'show advanced', 1

   2: GO

   3: RECONFIGURE

   4: GO

   5: sp_CONFIGURE 'Database Mail XPs', 1

   6: GO

   7: RECONFIGURE

   8: GO 

Una vez hecho esto podemos hacer un envío de correo de prueba para verificar que todo está funcionando como es debido.

Paso 3: Configurar la tarea programada

Ahora que ya tenemos el paquete de integración que se encargará de recopilar la información y meterla en un archivo CSV, y que tenemos definido el modo de envío de correo, lo que nos queda por hacer es crear la tarea programada correspondiente.

Desde el SQL Server Management Studio vete al nodo “Sql Server Agent” y dentro de la carpeta “Jobs” pulsa con el botón derecho para crear un nueva tarea. Dale un nombre y vete a la sección de “Pasos” (Steps en inglés).

Agrega un nuevo paso. Este paso será el encargado de lanzar el paquete que hemos creado y así obtener el archivo CSV a disco. Le das un nombre al paso, y en la lista de tipos elige “Paquete de SQL Server Integration Services”. En la pestaña “General” de abajo, escoges “Sistema de archivos” como origen del paquete o “SQL Server” en caso de haberlo guardado directamente a SQL Server. El resto de parámetros no hace falta tocarlos.

Añade un segundo paso. Este será el encargado de enviar por email el CSV que hemos creado en el paso anterior. Ponle un nombre y deja el tipo por defecto (Script de T-SQL). Elige la base de datos “msdb” en la lista. En el cuadro de texto para la consulta escribe lo siguiente:

   1: EXEC msdb.dbo.sp_send_dbmail

   2:     @profile_name = 'Notificaciones',

   3:     @recipients = 'destinatario@servidor.com',

   4:     @subject = 'Los datos automáticos de cada mes',

   5:     @body = N'Hola,,

   6:  

   7: Estos son los datos de xxxx que me pediste que te enviara una vez al mes. 

   8:  

   9: Saludos!'

  10:     @file_attachments = 'C:TemporalesDatosRecopilados.csv';

Lo que hacemos es usar el procedimiento almacenado de msdb llamado msdb.dbo.sp_send_dbmail. Le indicamos el nombre de un perfil de envío (ver paso anterior), el destinatario al que queremos enviar el archivo (si es más de uno s epuede separar con puntos y coma), el asunto del correo y el cuerpo del mismo. El parámetro @file_attachments sirve para indicar uno o más archivos (separados por punto y coma) que queramos enviar adjuntos al correo. En nuestro caso, como sabemos en dónde se guarda el archivo CSV basta con colocar ahí la ruta de éste.

En la sección “Programación” (Schedule) de la tarea programa la frecuencia que desees para su ejecución: una vez al día, a la semana, al mes, o como sea.

Con esto casi hemos terminado, pero aún queda un detalle importante…

Paso 4: Asignar los permisos correctos en el sistema de archivos

Para que este procedimiento funcione como es debido el usuario bajo el cual se ejecute la tarea programada debe tener acceso al archivo adjunto que queremos enviar.

Lo habitual es que al no tener asignado usuario alguno en la lista “Run as” de este paso en concreto, el paso se ejecute bajo el contexto del usuario nulo, es decir, de un usuario anónimo. Por ello lo mejor es guardar el archivo CSV en una carpeta específica y asignarle control total de la misma al usuario anónimo, para que no haya problemas para crear el archivo ni para enviarlo por email:

Con esto ya está todo listo.

Puedes probar que todo funciona correctamente pulsando con el botón derecho sobre la nueva tarea programada y eligiendo la opción de “Comenzar trabajo en el paso…”. En el diálogo que salta elige el paso 1 y pulsa “Comenzar”. Si todo es correcto verás color verde en cada uno de los pasos y recibirás el email con el archivo de datos.

Si hay algún problema haz doble-clic sobre la tearea y usa el enlace “Ver Historial del trabajo” para abrir la historia de ejecuciones y conocer el error detallado que te ha dado.

Si te dice que el archivo .csv no es válido es porque no has asignado correctamente los permisos de acceso.

En resumen

Una tarea tan común como generar un archivo de datos y poder enviarlo por email debería ser algo que nos debería facilitar mucho SQL Server. Lamentablemente no es así y hay que agudizar el ingenio para conseguirlo.

En lugar de ponerte a programar o perder mucho tiempo, si sigues los pasos que describo en este artículo (y que son de cosecha propia), podrás conseguir este objetivo de manera rápida y sencilla.

¡Espero que te sea útil!

Síguenos en Twitter:  @jm_alarcon   @campusMVP