Descargue un PDF de este Artículo
IntroducciónSegún la tendencia que hemos detectado por el tipo de proyectos que llevamos a cabo y que queda contrastado en periódicos digitales como Finanzas.com, una de las profesiones más demandadas tanto actualmente como en un futuro inmediato es la consultoría avanzada en inteligencia de negocio. Parece claro por tanto, que disponer de herramientas potentes, sencillas y fácilmente amoldables a nuestros requerimientos cambiantes va a propiciar un auge en la calidad de nuestros servicios. Como cualquier lector sabe, Solid Quality Mentors es el líder de soluciones global en tecnología Microsoft de la plataforma de datos, plataforma de datos que nos provee de entre otras herramientas y tecnologías, las que vamos a discutir en este artículo: SQL Server Integration Services (a partir de ahora nos referiremos a él como SSIS).
Como sabemos, todo proyecto de inteligencia de negocio lleva consigo irremediablemente la consolidación de información eficiente. Dicho proceso de consolidación se consigue con SSIS y su diseño es tan personalizado como potencialmente “similar” en todos los escenarios en los que hemos trabajado, que es una buena idea el automatizar su creación.
Desde SolidQ nos hemos percatado que en muchas ocasiones el cliente duplica bases de datos con la misma estructura para proporcionar lo que ellos denominan “particiones”, que no es más que un tipo de “Sharding” manual, o directamente tablas renombrándolas para tratar de conseguir particionado. Todo esto, no hace más que añadir complejidad al proceso de creación del Datawarehouse, que es donde debe residir la información unificada y “limpia” para poder explotarla correctamente.
Además de dicha complejidad añadida, en la ejecución de un proyecto de inteligencia de negocios en la que se involucran la creación de paquetes SSIS, se da la circunstancia que por tratarse de proyectos “vivos” y típicamente de media duración, los objetos fuente (tablas) en ocasiones son modificados para añadir columnas, cambiar tipos de datos, eliminar columnas,…con lo que en ocasiones obliga a rehacer trabajo en SSIS que como sabemos es fuertemente tipado.
En este artículo sentaremos las bases para poder entender cómo crear paquetes SSIS con los que conseguir que fácilmente podamos unificar toda la información a base prácticamente de 2 clics de ratón. Además, gracias a que utilizando dichas librerías no realizaremos intervención humana directa en la creación del paquete SSIS, un cambio en los objetos fuente o destino de nuestros paquetes, solo requerirán darle de nuevo al botón “generar” de nuestro generador. Por mi experiencia, si nuestro sistema pasa de las 50 o 60 tablas ya se ahorra tiempo haciéndolo de esta forma si tenemos que repetir el proceso solamente una vez porque al final nos damos cuenta de un detalle (pequeño o no) con la mitad de las tablas ya habríamos ahorrado tiempo escribiendo este código.
En resumen lo que vamos a ver es como crear una aplicación de Consola C# que va a generar paquetes de SSIS sin intervención humana (obviamente en algun momento le tendremos que indicar el origen y el destino, pero nada mas).
Requisitos para reproducir el artículo
Para la reproducción del artículo se recomienda disponer de los siguientes requisitos:
Visual Studio 2010_ La versión Express debería ser suficiente
SQL Server 2008 R2 (como mínimo será necesario versión standard para disponer de SSIS y probar nuestros paquetes generados)
Conocimientos de SSIS: Se parte de la base de que el lector conoce como crear paquetes SSIS, desplegarlos y utilizarlos.
Conocimientos altos en programación C# y manejo de Visual Studio 2010
Se parte de la base de que el lector conoce metodologías de programación orientadas a objetos y posee conocimientos en el manejo de Visual Studio 2010
Adicionalmente, en el site de solidq, puedes encontrar el código de la solución para descarga. Pincha aquí
El paquete dtsx
Antes de nada, hay que tener claro que el proceso de generación por código de paquetes SSIS no está bien documentado, por lo que mucho de lo que aquí vas a leer proviene tanto de prueba-error, como de ingeniería inversa analizando el comportamiento de objetos y el xml resultante. Con ello no quiero decir que haya violado ningún copyright, simplemente estoy diciendo que para saber cómo funciona correctamente un método, en ocasiones he tenido que ver qué generaba y compararlo con uno que había generado mediante la interfaz de Visual Studio.
Recuerda además, que un paquete de integration services, no es más que un XML que contiene la meta información necesaria para que el proceso dtexec.exe sea capaz de realizar lo que hemos indicado en él.
Para nuestro ejemplo usaremos la BBDD AdventureWorks y lo que haremos será mover información a una BBDD nueva a la que llamaremos Staging.
¿Qué pretendemos obtener?
Lo primero que recomiendo al lector, es que tenga claro lo que pretende obtener, y la mejor manera de hacerlo es realizando el paquete mediante la herramienta destinada para ello, Visual Studio. Una vez se tenga claro lo que se desea obtener, podremos entonces plantear darle una solución por código.
Se sobreentiende por tanto, que el lector plantea realizar “n” paquetes idénticos en los que cambie alguna particularidad. Por experiencia, es un escenario muy común.
Para este primer artículo, vamos a empezar con un código de lo más sencillo, para ir entrando las bases de lo que implica codificar SSIS programáticamente. Por tanto, el ejemplo más sencillo que se me ha ocurrido es simplemente mover datos de una tabla origen a una tabla destino.
Para conseguir esto, si estuviéramos utilizando el modelador de SSIS, deberemos añadir un componente DataFlow tal y como se puede apreciar en la Imagen 1, y dentro de ella, dos componentes OleDbSource y OleDbDestination.
Pero previamente a ello, lo normal es que añadamos nuestros objetos conexión, tal y como podemos ver en la Imagen 0
En la configuración del OleDbSource, indicaríamos qué objeto queremos como fuente (Imagen 3) y en OleDbDestination indicaríamos qué objeto queremos como destino (Imagen 4)
En resumen, mover los datos de la tabla HumanResources.Employee de la BBDD Adventureworks, a la tabla dbo.employee de la BBDD Staging.
NOTA: El único requisito de momento es que la tabla destino, a la que nosotros referenciamos como “employee”, debe existir.
Creación del paquete programáticamente
Para la construcción de nuestro generador de SSIS, vamos a optar por utilizar Visual Studio 2010. Esto igual te llama la atención, ya que la edición Visual Studio 2008 es la edición habilitada para crear paquetes SSIS. No obstante, en nuestro caso lo que vamos a hacer es utilizar las librerías que finalmente utiliza Visual Studio 2008, por lo que es indiferente el entorno de desarrollo utilizado, puesto que nosotros vamos a construirnos nuestro propio generador de SSIS.
Lo primero que debemos hacer es crearnos una solución que constará de:
Proyecto de librería (EnriqueCatalaSSISGenerator): Contendrá el código del generador de SSI
Proyecto de consola (DemoSSISGenerator): Se encargará de utilizar el proyecto de librería para crear el paquete
Lo siguiente será añadir las librerías necesarias para la elaboración de la librería del generador de SSIS.
Dichas librerías se encuentran dentro de la carpeta SDK de Microsoft SQL Server y son las que se pueden apreciar en la imagen 6
Una vez hecho esto, nos pondremos manos a la obra para la codificación de los métodos necesarios para nuestro Generador de código SSIS
NOTA: Recuerda que puedes descargar la solución de aqui
Librería de generación de código SSIS
Visto el tipo de paquete SSIS que queremos modelar, nos debemos poner manos a la obra para la codificación de los métodos necesarios, que deben cubrir los siguientes escenarios:
- Añadir objetos conexión OLEDB
- Añadir componentes Data Flow
- Añadir componentes OleDbSource
- Añadir componentes OleDbDestination
- Método principal de generación de paquete que utilice los anteriores
Añadir objetos conexión OLEDB
Para añadir una conexión OleDb a un paquete SSIS, lo único que debemos hacer es crear un objeto de la clase “ConnectionManager”, instanciando sus propiedades ConnectionString y Name.
Mientras que ConnectionString identificará una cadena de conexión válida, la propiedad Name, identificará el nombre de la conexión tal y como la vemos en el paquete al editarlo.
El código necesario para añadir conexiones OleDb a nuestros paquetes por código será este:
Añadir componentes DataFlow
Añadir componentes dataflow requiere añadir al conjunto de Executables del objeto Package, un tipo de ejecutable denominado “STOCK:PipelineTask”.
Añadir componentes OleDbSource
Si recordamos, en el escenario que queremos modelar, estamos tomando datos directamente desde una tabla, identificándola únicamente por su nombre (no hemos especificado ninguna select).
Lo primero que debemos hacer es crear un objeto estándar IDTSComponentMetaData100 que identificará nuestro objeto conexión (al origen, recuerda). Luego, ese componente, como queremos que sea un OleDbSource, lo identificaremos en su propiedad ComponentClassID indicando el valor “DTSAdapter.OLEDBSource”.
Una vez hecho eso, lanzaremos el método Instantiate() y ProvideComponentProperties() para que el objeto adquiera sus propiedades de OleDBSource.
Finalmente, debemos indicar en sus propiedades que será “AccessMode = 0” y que el tipo “OpenRowset” debe apuntar al nombre de tabla con esquema que le hayamos pasado (recuerda, en nuestro ejemplo será HumanResources.Employee.
Finalmente, inicializaremos sus metadatos (ver código try). Esta última parte, abrirá conexión contra SQL Server e intentará inicializarlos, si por lo que sea hemos proporcionado mal el objeto “connection”, nos dará error aquí.
Añadir componentes OleDbDestinationEn este momento, nos queda proporcionar el código que identifique al componente OleDbDestination. A diferencia del anterior método, en este caso no solo necesitaremos proporcionar un objeto conexión hacia destino y un nombre de tabla…en este caso, necesitaremos proporcionarle un componente origen (recuerda que en este caso, vamos a conectar directamente el componente OleDbSource al OleDbDestination 
Al igual que antes, el código de inicialización es muy similar, y solo va a cambiar:
ComponentClassID valdrá “DTSAdapter.OleDbDestination” puesto que queremos un objeto de tipo destino
Ahora, las propiedades asignadas serán:
AccessMode = 3 (carga identificando un nombre de objeto)
FastLoadOptions = “TABLOCK, CHECK_CONSTRAINTS” (queremos carga rápida)
OpenRowset = nombre de objeto destino (en este caso dbo.employee).
Una vez indicadas las propiedades, en el bucle foreach que se aprecia en el código, mapearemos las columnas del componente OleDbSource a este componente recientemente creado
Generación del paquete
Una vez dispuestos del código anterior, únicamente debemos utilizar los métodos creados anteriormente para generar nuestro paquete. Esto es tan fácil como lo que veis en el código siguiente:
Proyecto de consolaYa por último, en nuestra aplicación de consola, solo nos queda instanciar un objeto de nuestra clase generadora de código y proporcionarle los valores que queramos.
El resultado de su ejecución puede verse aquí (después de abierto y lanzado el paquete.dtsx)
ConclusiónComo se ha podido ver, el proceso de generación mediante código de SSIS es una tarea relativamente sencilla si se conocen los aspectos básicos de la misma. No obstante, su codificación entraña la necesidad de conocer por dentro las clases de generación de componentes y objetos, que por otro lado no hemos entrado en mucho detalle por tratarse de un primer artículo y no quiero asustar al lector con detalles demasiado profundos.
La idea final es acabar generando paquetes más complejos, como los que se pueden ver en la siguiente imagen, que poseen flujos variables y componentes más complejos con operadores “Merge Join” o el temido “Conditional Split” que tantas horas nos hacen pasar delante a la hora de su configuración.
Gracias a una librería como la que disponemos en SolidQ y que queremos dar una idea de su construcción al lector, desde Solid Quality Mentors, podemos generar miles de paquetes que realicen tareas realmente complejas en tan solo unos segundos, y lo que es mas importante…un cambio en los objetos inherentes al paquete (cambios de tipos de datos, nombres de columnas, de objetos…no tienen ningún coste alguno puesto que regenerar el paquete es cuestión de “dos clics de ratón”.
En siguientes artículos, veremos como seremos capaces de que incluso estos operadores sean capaces de inferir columnas y tipos de datos complejos sin intervención humana.
Aquí tenemos unas muestras de los tipos de paquete que genera el generador de SSIS de solidq sin intervención humana:
Sobre el Autor 
Enrique Catala Bañuls es mentor en el área relacional de Solid Quality Mentors. Es MCT, MCITP, MCTS y ha sido nombrado MAP 2010 (Microsoft Active Professional). Arquitecto y programador de las soluciónes HealthCheck, SCODA, SolidQDataCollector y del generador de SSIS de SolidQ. Centrado profesionalmente en bases de datos SQL Server, durante los últimos 5 años tiene su foco principal de operación en solución de problemas de rendimiento, escalabilidad, migraciones y alta disponibilidad. Además de impartir cursos oficiales de Microsoft, ha participado como speaker en eventos de lanzamiento de Microsoft España (Microsoft SQL Server 2008), en las 24h de conferencias de SQL PASS, miembro del nuevo SQL PASS Spain, en charlas del grupo de usuarios de Microsoft GuseNET y es ponente habitual de sesiones dentro del SolidQ Summit Madrid.