Dentro de los básicos del mundo de los datos, la capacidad de construir y desplegar procesos ETL (extracción transformacion y carga) es una de principales características de los sistemas modernos de datos.
Los procesos ETL llevan vigentes muchos años, aunque ahora hay tendencias a usar ELT es decir, construir las estructuras de destino antes de transformar la información, sobre todo con el auge del famoso BIG DATA. Lo hagas antes o despues, en un orden o en otro al final siempre hay un proceso de transformación. Por si alguien está interesado en esto, básicamente el paradigma ELT pretende dar una respuesta al tiempo real, o como decía un cliente al tiempo razonable. Incluso hay iniciativas que simplemente son un envoltorio sobre los datos que no se mueven y por tanto están siempre disponibles en tiempo real, a costa claro, de que la base de datos de origen «sufra» las consultas.

Más allá de todas estas teorías y del reto que puedan suponer, el artículo va orientado a lo que dice su título, Construir, desplegar y agendar procesos ETL con SQL Server Integration Services.

  1. Las herramientas que necesitaremos

Ha pasado por varios nombres, pero en el día de hoy se usan las SQL Server Data Tools, te sugiero que lo busques en tu buscador favorito, puesto que el enlance que te voy a poner a continuación seguramente quede obsoleto sin que yo me de cuenta Descarga la version actual de las SQL server Data Tools

Ojo, porque hay comos dos versiones, una de las data tools es para el mundo relacional y se encarga de la generación y mantenimiento de bases de datos. Es una interesante herramienta pero no es de la que hablamos en este artículo. Síguele la pista sobre todo si quieres automatizar los despliegues de aplicaciones en lo que en el momento de la escritura de este artículo se llama DevOps.

Una vez que las descargues, en tu versión de Visual Studio te aparecerá un nuevo menú de proyectos, en el que tendrás un apartado llamado Business Intelligence (la verdad es que no se que pone si el Visual Studio está en español), con los proyectos tipo Analisys Services, ReportingServices y el que nos interesa a nosotros, Integration Services.

2. Crea tu primer proyecto SSIS

La intención de este artículo no es hacer un tutorial detallado del uso de SSIS, digo esto porque no quiero que el artículo pese 100 páginas y sea medio libro, por eso vamos a mostrar un paquete ya creado con una somera explicación de cada una de las partes .Todo esto con el objeto de dar una visión general del proceso completo, así pues una vez creado nuestro paquete podremos compilarlo y desplegarlo.

Los paquetes de SSIS tienen dos tipos de objetos, los flujos de control y los flujos de datos. Los flujos de control son lo que en una aplicación en cualquiera de tus lenguajes favoritos son los bucles y estructuras de control del flujo del programa. En ellos decides que trozos se ejecutan y cuales no, decides que trozo se paraleliza y que trozo se hace en secuencia, si hay que iterar por tablas etc etc. Para todo esto tiene componentes SSIS y además no se necesitan conocimientos de desarrollo profundos para usarlos, simplemente conocer que hace cada componente y como se va a comportar.

Un ejemplo terminado de flujo de control que yo uso de forma habitual es el siguiente. (verás que es bastante completo). Sería muy costoso mantener ese nivel de calidad, pero en mi caso los genero automáticamente con el Framework que tenemos en SolidQ para desarrollar estas aplicaciones me puedo permitir el que sean así de terminados.

El primero de los objetos «Check load Active» es una consulta SQL a una tabla de meta-datos, en esa tabla guardo un montón de información sobre la ejecución del proceso, uno de los valores que guardo es si está o no activa para ejecutarse. De esta forma si una vez desplegado el paquete quiero que no se ejecute, me basta con cambiar ese valor y no tengo que desplegar de nuevo una solución. De ahí que la flecha verde que une el flujo tenga un fx, ese valor es el que comprueba si ha de seguir ejecutándose o no.

El segundo componente es una llamada a un procedimiento almacenado que marca el inicio de la carga, ahí guardo información como el nombre del paquete la hora de comienzo, el tipo de proceso y más información relevante para hacer después análisis de ejecuciones. Esta información se completará al final con el resultado de la ejecución (satisfactorio o erróneo)

Después lanzo script de pre-ejecución. Estos scripts los uso en ocasiones para hacer tareas previas al movimiento de datos en sí, reconstrucción de estadísticas, creación de falsas tablas temporales, actualización de valores relevantes, resúmenes de información, o cualquier otra tarea que necesite antes de ejecutar mi proceso de sincronizar. En esta ocasión solo hay un comando, pero en procesos más complejos puedo tener varios.

A continuación busco el ultimo valor leído. Esto es porque el proceso, en este caso, es incremental, imaginemos que es por una fecha. En una tabla de configuración tengo almacenada la última fecha que leí, ahora en este componente leo esa fecha y la asigno a una variable. Después usaré esa variable para filtrar la información de origen exclusivamente a la que sea mayor o igual que la última fecha que leí. (si estás pensando que eso no detecta borrados… es correcto.. para eso hay otros procesos pero no es el caso de detallar todos y cada uno de los pormenores en este artículo)

Después se puede observar que entramos en el proceso de carga de datos. Lo primero que se hará es limpiar la tabla de cambios. Esto significa que en mi forma habitual de proceder, para cada tabla que voy a sincronizar creo una tabla paralela para guardar las operaciones que han cambiado, de esta forma lo que hago es siempre insertar cambios en esta tabla, para despues actualizar los datos de la tabla original con los datos de la tabla de cambios. ¿Porqué? El motivo es que los componentes oleDB command de SSIS en un flujo de datos se ejecutan por cada fila, si se actualizaran muchas filas el proceso sería estúpidamente largo en tiempos prefiero hacer un solo comando update, o delete para actualizar todas las filas. Decir también que cuando ha sido necesario he creado proceso que hacen estas actualizaciones por bloques para evitar que sufra el log de transacciones.

Lo más relevante tras esta operación es el flujo de datos, en él también voy a dar una explicación somera de lo que se hace puesto que no pretendo aquí crear un tutorial de creación de flujos de datos en ssis.

En mi caso lo primero que hago es usar un componente OleDB Source (llamado HLP) para leer datos del origen, ese componente es el que filtrará el query de origen para traerme solo los datos que han cambiado.  Una vez que tenemos esos datos, es posible, que como parte de nuestra transformación, algunos tipos de datos cambien, particularmente si nuestro origen es de un sistema gestor de bases de datos distinto al de  destino. Como puede observarse en el camino de error (el rojo) si no se puede hacer esa transformación prevista, simplemente llevaré los registros a una tabla de error, que me permita saber que algo no está funcionando bien en mi proceso. A continuación genero un Hash con todas las columnas de la tabla, excepto las que forman parte de la clave primaria. Uso el componente Multiple hash que os recomiendo encarecidamente. Con eso me ahorro de escribir complejos codigos de comprobacion teniendo en cuenta posibles nulos etc. Simplemente para cada clave primaria que coindice, veo si coincide o no el hash que ya había calculado vs el hash que acabo de calcular.

Una vez hecho ese cálculo uso el componente lookup, para encontrar los registros que ya tengo y poder distinguir entre  insertados y modificados (y no cambiados en absoluto).

Además de esto contaré las filas que pasan por cada flujo y marcaré la operación real (actualizacion o inserción) que sufre el registro, dejando el resultado en la tabla de cambios.

Como podréis imaginar después hay un post-proceso en el que las filas modificadas se modifican realmente y las insertadas se llevan a su lugar y un proceso adicional -cuando es necesario- de detección de eliminados, completando el proceso de sincronización.

 

 

 

 

 

Deja un comentario

Tu dirección de correo electrónico no será publicada. Los campos obligatorios están marcados con *