Estudio del servicio de integración de datos del SQL Server (SSIS)

Texto completo

(1)Facultad Matemática, Física y Computación Licenciatura en Ciencia de la Computación. TRABAJO DE DIPLOMA Título: “Estudio del servicio de integración de datos del SQL Server (SSIS)”. Autor: Eduardo Leonel Torres Calzada Tutores: Dra. Beatriz E. López Porrero Villa Clara 2013.

(2) DECLARACIÓN DE AUDITORÍA. Hago constar que el presente trabajo fue realizado en la Universidad Central Marta Abreu de Las Villas como parte de la culminación de los estudios de la especialidad de Ciencias de la Computación, autorizando a que el mismo sea utilizado por la institución, para los fines que estime conveniente, tanto de forma parcial como total y que además no podrá ser presentado en eventos ni publicado sin la autorización de la Universidad.. ____________________________. Firma de los autores. Los abajo firmantes, certificamos que el presente trabajo ha sido realizado según acuerdos de la dirección de nuestro centro y el mismo cumple con los requisitos que debe tener un trabajo de esta envergadura referido a la temática señalada.. _________________________. __________________________. Firma de los tutores. Firma del jefe del Seminario. I.

(3) PENSAMIENTO. “La inteligencia de negocio es la capacidad e comprender las interrelaciones de los hechos presentados para orientar la acción hacia la meta deseada pues los datos por sí mismos son incapaces de producir beneficios” Hans Peter Luhn, investigador de IBM, 1958. II.

(4) DEDICATORIA. A mi familia por la buena educación que me han dado y por siempre estar de mi lado. A mi abuelos Ana y Urbano, que aunque no me han visto triunfar, han estado ahí, apoyándome, incluso en medio de la derrota. Los quiero mucho a todos. III.

(5) AGRADECIMIENTOS A mi mamá, por saberme amar desde una perspectiva diferente, por acompañarme hasta donde estoy hoy. Por no ser la madre que ve perfección en sus hijos. Gracias por mostrarme el camino a seguir, y que no existe en el mundo mejor cobija que tu compañía. A mis abuelas Omaida y Hortensia, que son mis otras madres; soñé durante 5 años con este día y con otros que están por venir. A mi padre, por llegar a tiempo;... por estar ahí cuando se te necesita, por haber sido el motivo. A Pedro, mi hermano, mi refuerzo: hablar del amor de hermanos es redundar. Yo te quería desde antes de llegar. A mi tío Alberto, mi inofensivo guardián, porque ni dedicando la vida entera a pagarle sus bondades, podría saldar esta inmensa deuda de gratitud. Luis, no somos padre e hijo, pero eres el amigo, ese que no está todos los días pero que su gesto y llega en el momento más preciado. Eres quien puso la escalera en mi vida y me dijo: ¡sube!, y para cuando pensara caer, aparecer. A Annia porque si fuera la mitad de lo que ella cree, nuestro afecto podría tirarse contra las más altas murallas y derrumbarlas y entonces, estaría más conforme conmigo mismo. Leno, Lino, Yess, Yan: a ustedes les doy gracias porque serán siempre la huella más preciada de mi paso por la universidad. Son amigos como pocos, están donde deben y cuando deben. Si no existieran, hoy mismo no sería quien soy A Alejandro y Miguel, porque su lealtad me glorifica, porque confío más en la perennidad de nuestra amistad que en mí mismo. A mis amigos todos Jose, Rocío, Yanela, Isabel, Alí, Víctor porque son el refugio más seguro, porque son ellos los únicos capaces de reunir todas las virtudes humanas. A ellos mi infinito agradecimiento por aparecer de uno en uno, por ahí, por los confines de la vida.. IV.

(6) A mi tutora, por ser un hecho fehaciente de que existe siempre alguien dispuesto a ayudarte en momentos difíciles, le estaré eternamente agradecido. A la Universidad Central, porque en sus anchas calles pasé unos de los mejores años de mi vida; por como soy, y sobre todo, por lograr crecer con persona independiente junto a las amistades que ahí hice. Gracias al guerrero más ecuánime que conozco, que sabe seguir adelante en la madrugada y partir bajo el frío a pelear sus sueños, mientras todos descansan. Un hombre que teme a las despedidas y que aunque no siempre está seguro de todo, siempre cumple, transparente, amigo, espontáneo, a veces ingenuo. Un hombre que sabía que el camino no era fácil, no es fácil, y todavía anda por ahí, con una sonrisa trasnochada, pero sonrisa al fin. Yo , Gracias. V.

(7) RESUMEN. La necesidad de integración de datos en el mundo actual es cada vez mayor. La cantidad de información que se genera a diario es cada vez más grande y diversa. Esta necesidad está dada porque la forma de representar de la información en las diferentes fuentes es también muy diversa; de allí que al integrarse deba ser preparada para garantizar en los procesos de tomas de decisiones y de cualquier análisis o consolidación que se realice la confiabilidad de los resultados. La selección de una herramienta que de soporte a las necesidades del proceso de integración constituye un elemento muy importante en el éxito del mismo. En este trabajo se realiza un estudio de la herramienta de integración de datos del SQL Server, para determinar sus posibilidades de satisfacer la creación de las complejas soluciones que se presentan en el ámbito de la creación y mantenimiento de los almacenes de datos. En el presente trabajo se mostrará el uso de la herramienta, su arquitectura y su forma de organizar los datos para lograr la integración de los mismos. Se expondrá el paquete, como núcleo principal de la herramienta de integración, con todos sus componentes que lo integran y sus interrelaciones. Para finalizar se expondrá un ejemplo de cómo funciona la herramienta ante un problema real, que realiza una tarea necesaria en la creación de un almacén de datos: poblar una dimensión.. VI.

(8) ABSTRACT. The need for data integration in today's world is growing. The amount of information generated every day is becoming larger and more diverse. This need is given for how to represent the information in the different sources is also very diverse, and hence they must be prepared to integrate to ensure the processes of decision making and any analysis or consolidation is performed reliability results. The selection of a tool that supports the needs of the integration process is a very important element in the success. In this work we make a study of data integration tool of SQL Server, to determine their ability to meet the complex creating solutions presented in the field of creation and maintenance of data warehouses. In this paper will show the use of the tool, its architecture and how to organize data for the integration thereof. It will present the package as core integration tool, with all components that comprise it and their interrelationships. Finally will be present an example of how the tool to a real problem, that performs a necessary task in creating a data warehouse: populate a dimension.. VII.

(9) TABLA DE CONTENIDOS Contenidos INTRODUCCIÓN .............................................................................................................................. 9 CAPÍTULO 1. ASPECTOS TEÓRICOS DEL PROCESO DE INTEGRACIÓN DE DATOS ......... 4 1.1 La integración de datos ............................................................................................................. 5 1.1.1 Tipos de herramientas de integración de información ....................................................... 5 1.1.2 Principales tecnologías empleadas en la integración de datos ........................................... 6 1.1.3 Las Técnicas de Integración de Datos .............................................................................. 10 1.1.4 Tendencias futuras............................................................................................................ 11 1.2 Extracción, Transformación y Carga....................................................................................... 12 1.2.1 Definición de ETL ............................................................................................................ 12 1.2.2 Procesamiento paralelo en sistemas ETL ......................................................................... 20 1.2.3 Desafíos actuales en el proceso de ETL ........................................................................... 21 1.3 Conclusiones parciales. ........................................................................................................... 22 CAPÍTULO 2. PRESENTACIÓN DEL SSIS .................................................................................. 23 2.1 Servicio de integración de datos del SQL Server .................................................................... 24 2.2 Usos típicos del servicio de integración de datos .................................................................... 24 2.3 Arquitectura del servicio de integración de datos. .................................................................. 27 2.4 Arquitectura de un paquete de SSIS. ....................................................................................... 29 2.5 Conclusiones parciales. ........................................................................................................... 53 CAPÍTULO 3. PRESENTACIÓN DEL CASO DE ESTUDIO. ...................................................... 54 3.1 Descripción del caso de estudio .............................................................................................. 55 3.2 Problemas que deben resolverse con el sistema de ETL ......................................................... 56 3.3 Descripción de las soluciones. ................................................................................................ 57 3.3.1 Soluciones a los problemas del origen de datos en Access: ............................................. 58 3.3.2 Soluciones a los problemas del origen de datos en Excel: ............................................... 62 3.4 Ejecutar el proyecto solución. ................................................................................................. 64 3.5 Conclusiones parciales. ........................................................................................................... 65 CONCLUSIONES ............................................................................................................................ 66 RECOMENDACIONES ................................................................................................................... 67 REFERENCIAS BIBLIOGRÁFICAS .............................................................................................. 68. VIII.

(10) INTRODUCCIÓN. La información es considerablemente importante en los procesos históricos y de trayectoria de las organizaciones; el avance de las tecnologías de Inteligencia de negocio obliga a migrar a herramientas más potentes para trabajar con grandes cantidades de datos que se acumulan de forma útil. Dado que la información está directamente relacionada con la calidad de la toma de decisiones y los riesgos asumidos en el cumplimiento de las metas de la organización, es importante que en la migración de los datos se mantenga su valor de una forma óptima. En este proceso de centralización de los datos debe hacerse todo lo posible por corregir los errores que puedan surgir, a pesar de la complejidad, la heterogeneidad, y el error humano en los mecanismos de captura de datos de un sistema. El SQL Server como gestor de datos en su evolución ha ido incorporando cada vez más herramientas que facilitan esta tarea y en particular a partir del SQL Server 2005 se ofrece el SSIS como plataforma en que se pueden desarrollar una serie de operaciones que contribuyen a la integración de datos a través de proyectos, permitiendo crear soluciones robustas y complejas de ETL de una forma fácil. Este trabajo contribuye a esclarecer las distintas posibilidades que brinda esta herramienta para posteriormente realizar una comparación con otras que han ido apareciendo en el mercado con este propósito, como software libres. A partir de esta problemática se plantea el siguiente problema de investigación: En el proceso de migración de datos, desde diversas fuentes, conocido como Extracción, Transformación y Carga (ETL); es importante que los datos posean calidad al ser migrados, por tanto, es necesario encontrar el programa lo suficientemente eficaz que lo permita. El objetivo general de la investigación consiste en realizar un estudio que permita caracterizar el proceso de integración de datos y establecer en qué medida se corresponde el Servicio de integración del SQL Server con esa caracterización.. IX.

(11) Introducción. De este objetivo se derivan los objetivos específicos siguientes: 1. Realizar el estudio de todos los aspectos teóricos relacionados con el proceso de integración de datos. 2. Describir las posibilidades que brinda el servicio de integración de datos con respecto a la caracterización lograda en el objetivo anterior. 3. Realizar y documentar un caso de estudio con el servicio de integración de datos del SQL Server (SSIS). Las preguntas de investigación planteadas son:  ¿Cuáles son las principales causas o escenarios que demandan la realización del proceso de integración de datos?  ¿Cuáles son las causas de la ineficiencia de los programas actuales de ETL en los grandes volúmenes de datos y cómo solucionarlo?  ¿Cuáles son los principales aspectos invariantes que deben estar formando parte del proceso de integración de datos?  ¿Satisface el SSIS las posibilidades de realizar procesos de integración y ETL?  ¿Cómo se prepara en el SSIS un proyecto que dé solución a un problema de integración?  ¿En qué aspectos resulta el SSIS estar por debajo de la caracterización teórica y en cuáles la supera? La investigación se justifica por la importancia que ha adquirido en el mundo actual la integración de datos desde diferentes fuentes. Particularmente este proceso resulta importante en los almacenes de datos en el proceso de extracción transformación y carga (ETL), aunque también es aplicable a cualquier escenario que requiera reunir los datos desde varias fuentes en que los mismos pueden presentarse en diferentes formatos y representaciones, con una buena calidad de procesamiento de los datos. El SQL Server como gestor de datos en su evolución ha ido incorporando más herramientas que facilitan esta tarea. En particular a partir del SQL Server 2005 se ofrece el SSIS como plataforma en que se puede desarrollar una serie de operaciones que contribuyen a la. 2.

(12) Introducción. integración de datos a través de proyectos, permitiendo crear soluciones robustas y complejas de ETL de una forma fácil. Este trabajo contribuye a esclarecer las distintas posibilidades que brinda esta herramienta para posteriormente realizar una comparación con otras que con este propósito de la integración han ido apareciendo en el mercado como software libres. La tesis está estructurada en tres capítulos: Capítulo 1. Aspectos teóricos del proceso de integración de datos: abordará todos los aspectos teóricos que tienen que ver con la investigación de Integración de datos y ETL, cuáles son los escenarios que demandan este proceso, y la caracterización del mismo. Capítulo 2. Presentación del SISS: describirá el servicio de la herramienta de SSIS, la arquitectura de los paquetes, las soluciones, proyectos, el paquete y cada uno de sus componentes. Capítulo 3. Presentación del caso de estudio: describirá a través de un caso de estudio una solución de integración con el uso del SSIS.. 3.

(13) Capítulo1: Aspectos teóricos del proceso de Integración de datos. CAPÍTULO 1. ASPECTOS TEÓRICOS DEL PROCESO DE INTEGRACIÓN DE DATOS.

(14) Capítulo1: Aspectos teóricos del proceso de Integración de datos. CAPÍTULO 1. ASPECTOS TEÓRICOS DEL PROCESO DE INTEGRACIÓN DE DATOS Actualmente en todas las esferas de la vida tanto personales, empresariales como globales, el volumen de los datos crece exponencialmente, es por eso que su organización se ha convertido en una tarea muy importante. Disciplinas como la integración de datos, la migración, la sincronización, la inteligencia de negocios, entre otras, permiten hacerlo. En este capítulo se explican y describen las ideas y conceptos de este complejo paisaje del campo de la gestión de datos, específicamente el tema de integración y dentro de este, el subtema de la extracción, transformación y carga (ETL por sus siglas en inglés). 1.1 La integración de datos La integración de datos consiste en combinar datos de varias fuentes diferentes, que se almacenan utilizando diversas tecnologías, proporcionando a los usuarios una vista unificada de los datos.(Software, 2013, Sybase Iberia, Hector García Molina) El proceso de integración de datos es significativo en una variedad de situaciones, que incluyen tanto la comercial (cuando dos empresas similares deben fusionar sus bases de datos) así como en dominios científicos (por ejemplo cuando se combinan los resultados de investigación de diferentes repositorios de bioinformática). Aparece con mayor frecuencia en la medida que el volumen y la necesidad de compartir los datos existentes aumenta (Lane, 2006). Este proceso se ha convertido en el centro de un extenso trabajo teórico y de numerosos problemas abiertos para la investigación científica. Sobre la integración de datos se ha desarrollado una teoría que constituye un subconjunto de la teoría de base de datos y que formaliza los conceptos subyacentes del problema en la lógica de primer orden. La aplicación de la teoría da indicaciones sobre la viabilidad y las dificultades de la integración de datos. (Lenzerini, 2002) 1.1.1 Tipos de herramientas de integración de informaci ón En (Haas, 2008) se relacionan una serie de tipos de herramientas que son usadas en la práctica en el proceso de integración de información, entre las cuales se encuentran las siguientes:. 5.

(15) Capítulo1: Aspectos teóricos del proceso de Integración de datos.  Herramientas de ETL: estas herramientas están dirigidas a resolver los problemas de la carga de datos hacia un almacén (Kimball, 2004), simplificando la programación de ficheros scripts. Una típica herramienta de ETL debe incluir un repertorio de operaciones de limpieza y de reformulación al estilo SQL para recuperar, combinar y ordenar los datos, así como también operaciones de planificación para controlar cuando realizar la carga periódica o la actualización del almacén de datos.  Herramientas de Enterprise Information Integration (EII): las cuales están dirigidas específicamente a resolver la integración de datos virtuales, que consiste en trasladar las solicitudes de los usuarios a las fuentes e integrar los resultados como si esto hubiera sido hecho sobre una única base de datos (Halevy, 2005). Estos productos EII varían en dependencias de los orígenes de datos que serán integrados. Por ejemplo, algunos se centran en la integración de bases de datos SQL, otros en la integración de servicios Web y otros en bases de datos de bioinformática.  Herramientas de conversión objeto-relacional (object-relational mapper): Son herramientas que permiten el entendimiento entre los programas de aplicación, que generalmente están hechos en lenguajes orientados a objetos y los datos que ellos acceden, los que generalmente están en bases de datos relacionales. La correspondencia entre ambos se expresa a través de un programa en un lenguaje de alto nivel que al ser compilado traduce las solicitudes y actualizaciones hechas sobre la interfaz orientada a objeto a solicitudes y actualizaciones sobre la base de datos relacional (Melnik, 2007) y  Herramientas de gestión de documentos: que realizan la integración de los documentos de grandes empresas, facilitando la colaboración y evitando la duplicidad de trabajo. Las herramientas de este tipo hacen los documentos disponibles a través de páginas Web o de un sistema manejador de contenidos. En el núcleo de estas herramientas están un grupo de tecnologías importantes, las cuales serán descritas en el próximo epígrafe. 1.1.2 Principales tecnologías empleadas en la integración de datos Entre las principales tecnologías que son empleadas en el núcleo de las herramientas de integración de datos están:. 6.

(16) Capítulo1: Aspectos teóricos del proceso de Integración de datos.  El lenguaje XML debido a que su flexibilidad lo convierte en un formato interesante para la integración de datos desde fuentes heterogéneas con diferentes representaciones de los datos.  Estandarización de esquemas: esta tecnología es empleada ya que en ocasiones las fuentes a integrar no coinciden en sus respectivos esquemas, en cuyo caso se define un esquema único al cual ellas deben converger. Esta tecnología ha sido desarrollada para tipos genéricos de datos tales como los sistemas de información geográficos, o los sistemas de información ingenieril, así como también para aplicaciones de dominio específico como por ejemplo, las aplicaciones de diseño asistido por computadoras (Computer Aided Design, CAD).  Limpieza de datos (data cleaning): producto de que la misma información o información relacionada es descrita en diferentes fuentes, con frecuencia se encuentran inconsistencias o sencillamente errores en los datos, que requieren ser corregidos.  Correspondencias de esquemas (schema mapping). Esta tecnología enfrenta el reto de establecer como es la correspondencia entre cada uno de los esquemas de los orígenes de datos y el esquema integrado, definiendo transformaciones que deben ser llevadas a cabo a través de programas, por ejemplo, las herramientas de ETL pueden generar estas transformaciones en script y un sistema EII podrá generarlas a través de consultas en un lenguaje de solicitudes como el SQL.  Comparación de esquemas (schema matching). Esta tecnología determina la coincidencia de esquemas utilizando para ello técnicas de aprendizaje automático o heurísticas basadas en información disponible en cuanto a similitud de nombres, similitud de tipo, entre otras. Otras tecnologías descritas en (Haas, 2008), son la búsqueda de palabras claves, la extracción de información y la web dinámica. Existen varias formas de lograr la integración de bases de datos o de otras fuentes de datos distribuidas, entre ellas las más usadas son:(Hector García Molina)  Las bases de datos federadas  La construcción de almacenes de datos (warehousing)  El uso de una capa de software intermedia (mediation).. 7.

(17) Capítulo1: Aspectos teóricos del proceso de Integración de datos. En todas estas direcciones, constituye un elemento clave el hecho de que los datos tienen que ser transformados al ser extraídos de las fuentes. La integración de datos debe verse como la convergencia de varias tecnologías existentes y de algunas nuevas que están emergiendo. Este proceso reúne tecnologías que son necesarias desde el lado operacional del negocio con otras que son necesarias desde el lado de la toma de decisiones del negocio. La integración de datos trata con la incorporación de todos los tipos de datos organizacionales en un todo unificado. (Landscape) En una estrategia de integración de datos deben estar presentes los aspectos de conectividad y metadatos. Cualquier solución debe brindar la posibilidad de conectividad a través de acceso nativo utilizando los utilitarios comunes y las formas de acceso abierto (tales como Open Database Conectivity: ODBC), a las principales fuentes de datos estructurados, como bases de datos relacionales, archivos planos, ERP (Enterprise Resource Planning, por sus siglas en inglés) y a los lenguajes de marcas como XML para leer y escribir. Las capacidades de conectividad de los datos deben también facilitar el acceso a la información desde diferentes sistemas operativos tales como UNIX, Windows, z/OS, preferiblemente sin tener que hacer uso de ficheros intermedios. Además la conectividad debe brindar soporte para leer y escribir desde colas de mensajes y la habilidad de recibir y enviar datos para y desde el servicio Web. Debe también facilitar la conectividad a fuentes de datos no estructurados. (Landscape) Los metadatos, por su parte, hacen posible reunir los datos de las diferentes fuentes en un todo único brindando un valor agregado a los mismos. Además permiten establecer cuando el dato es movido, cómo ha sido cambiado, qué reglas del negocio fueron aplicadas y el impacto que el cambio pudo tener. De allí que es un fallo no dar el énfasis necesario a los metadatos, lo que resultará en futuros problemas que generarán un gran costo a la organización. (Landscape) Otras áreas que deben estar presentes en las estrategias de integración de datos son:  Almacenamiento de datos (Data Warehousing). 8.

(18) Capítulo1: Aspectos teóricos del proceso de Integración de datos. La utilización más difundida y conocida de integración de datos es la construcción de los almacenes de datos. El principal beneficio de un almacén de datos es que permite a una empresa llevar a cabo análisis basados en los datos del almacén, lo que no sería posible solo con los datos disponibles en los sistemas de origen, pues en estas fuentes, aún cuando los datos pueden tener los mismos nombres, podrán referirse a entidades diferentes. En la construcción de un almacén de datos los datos no son “entrados”, tal y como ocurre en los sistemas transaccionales u operacionales, en su lugar el almacén es poblado tomando los datos que ya existen en otras fuentes: sistemas operacionales y otros ficheros. El proceso de importación y manipulación de los datos transaccionales en el almacén de datos es referido como Extracción, Transformación y Carga (Extraction, Transformation and Loading, ETL). (Alkis Simitsis National Technical University of Athens et al., 2005) Cualquier estrategia de integración de datos debe posibilitar el construir y mantener almacenes y mercados de datos a través del proceso de ETL.  Migración de datos (Data migration) La migración de datos hace referencia al traspaso de información entre bases de datos. Por ejemplo, si se tiene una aplicación sobre una base de datos como Access y posteriormente "crece" de manera que hace falta un sistema gestor de bases de datos potente, lo más seguro es que se use Oracle, SQL Server u otros sistemas similares. En este caso, los datos, que están en formato "Access" deberán pasar al formato de las bases de datos del gestor escogido. La migración de los datos consiste en convertir los datos desde un sistema de base de datos a otro. Este proceso puede conducir a la creación de tablas nuevas o a la modificación de las existentes, y posiblemente a cambios en algunos tipos de datos que existen en un gestor base de datos pero no en el otro, entre algunas de las transformaciones. (Wikipedia, 2012c). 9.

(19) Capítulo1: Aspectos teóricos del proceso de Integración de datos. Actualmente la mayoría de los SGBD incluyen herramientas de ayuda a la migración, el gestor Microsoft SQL Server la brinda como parte del SQL Server Integration Services (SSIS).  Aplicación de empresa/Integración de la información (Enterprise Application/Information Integration) El área de aplicación de empresa/Integración de la información presupone el uso de software y de los principios de la arquitectura de sistemas para integrar un conjunto de aplicaciones de una empresa. Incluye la integración de datos, denominada Enterprise Information Integration (EII); la integración de procesos; la independencia de proveedor: en que se extraen las políticas o reglas del negocio de las aplicaciones y se implementan en un sistema Enterprise Application Integration (EAI), de forma que cualquiera de las aplicaciones usadas pueda ser cambiada sin que dichas reglas de negocio deban ser re-implementadas y el patrón de diseño o Interfaz común: que consiste en que un sistema EAI puede actuar como el front-end de un cúmulo de aplicaciones, proporcionando una interfaz de acceso única y consistente a esas aplicaciones, aislando a los usuarios de la interacción con distintas aplicaciones.  Gestión de datos maestros (MDM): MDM es una aplicación de integración de datos. Dentro de MDM hay diferentes sub-aéreas tales como MDM Cliente (a la que se denomina integración de datos de clientes), MDM Producto, MDM Finanzas y algunas otras. Estas aplicaciones emplean tecnologías y técnicas de integración de datos para resolver problemas de negocio, que añaden valor por el soporte que ofrecen a la semántica de negocio y los metadatos específicos al área del problema de negocio a que está siendo dirigido. (java.net) 1.1.3 Las Técnicas de Integración de Datos Hay varios niveles de organización en el que se puede realizar la integración.  Integración manual o interfaz de usuario común: los usuarios operan con toda la información relevante accediendo a todos los sistemas fuentes o interfaces web. No existe una vista unificada de los datos.. 10.

(20) Capítulo1: Aspectos teóricos del proceso de Integración de datos.  Integración Basada en Aplicaciones: requiere que las aplicaciones particulares realicen todos los esfuerzos necesarios en el proceso de integración. Este enfoque es manejable sólo en el caso de un número muy limitado de aplicaciones.  Middleware Data Integration: transfiere la lógica de integración de aplicaciones particulares a una nueva capa intermedia. Aunque la lógica de la integración no está implementada en las aplicaciones, las aplicaciones deben participar parcialmente en la integración de datos.  Virtual Integration: los datos permanecen en los sistemas de origen y se definen un conjunto de vistas para gestionar y acceder a la visión unificada del cliente a través de toda la empresa. Por ejemplo, cuando un usuario accede a la información del cliente, los detalles particulares del cliente se adquieren de forma transparente sobre el sistema fuente. La principal ventaja de la integración virtual es la latencia casi cero de la propagación de las actualizaciones de datos desde el sistema de origen a las vistas consolidadas, no hay necesidad de almacenar por separado los datos consolidados. Sin embargo, entre las desventajas se incluyen la limitada posibilidad de mantener la historia de los datos y la gestión de versiones, la limitación de aplicar el método sólo a las fuentes "similares" de datos y el hecho de que el acceso a los datos de usuario genera una carga adicional en los sistemas de origen, que pueden no haber sido diseñado para ese propósito.  Physical Data Integration: determina la creación de un nuevo sistema que mantiene una copia de los datos de las fuentes con el propósito de que los datos sean almacenados y gestionados de forma independiente a los sistemas de origen. El ejemplo mejor conocido de este enfoque, como ya se he mencionado, son los almacenes de datos. Entre las ventajas principales se encuentran la gestión de versiones y la combinación de datos desde fuentes muy diferentes (mainframes, bases de datos, archivos planos, etc.). La integración física, sin embargo, requiere un. sistema. separado. para. manejar. los. grandes. volúmenes. de. datos.(http://www.dataintegration.info, 2011) 1.1.4 Tendencias futuras La integración de datos como línea de investigación constituye un campo en pleno desarrollo debido no solo a la actual evolución ingenieril sino también a la propia evolución. 11.

(21) Capítulo1: Aspectos teóricos del proceso de Integración de datos. dentro de este campo. Inicialmente estuvo estimulada por los demandas del sector empresarial, luego en la primera década del 2000 por la creciente necesidad de integrar la gran cantidad de datos disponible en la Web y más recientemente por lo desafíos que se dan producto de la necesidad creciente de mejorar las búsquedas basadas en la Web, el almacenamiento en las nubes, los servicios de integración basados en la Web y las interfaces abiertas para aplicaciones Web tales como las redes sociales, entre otros elementos. 1.2 Extracción, Transformación y Carga El proceso de Extracción, Transformación y Carga puede ser visto como la máxima expresión de la integración de datos en el escenario de la construcción de los almacenes de datos, aunque el mismo va más allá de este contexto, posibilitando la resolución de tareas rutinarias llevadas a cabo por los administradores en las bases de datos y en la tareas de integración de sistemas legados con las nuevas aplicaciones, este epígrafe se centrará en el proceso de ETL en los almacenes de datos. 1.2.1 Definición de ETL Es el proceso que permite a las organizaciones mover datos desde múltiples fuentes, reformatearlos, limpiarlos, y cargarlos en otra base de datos, mercados de datos, o en almacenes de datos para analizarlos, o en otros sistemas operacionales para apoyar un proceso de negocio. (Wikipedia, 2010) En (Prix, 2010) y (Alkis Simitsis, 2005) se expresa que las herramientas ETL, son consideradas piezas de software fundamentales en la construcción, explotación y evolución de un almacén de datos, responsables de la extracción de los datos desde las diferentes fuentes, su limpieza, adecuación e inserción en los almacenes de datos proporcionando las siguientes funcionalidades:  La extracción de la información relevante desde el lado de las fuentes,  La transportación de esta información al área de datos intermedia (Data Staging Area, DSA),  La transformación requerida de esta información ( personalización e integración),. 12.

(22) Capítulo1: Aspectos teóricos del proceso de Integración de datos.  La limpieza de datos sobre la base de las reglas de negocio de la base de datos, metadatos y restricciones, y  La propagación y carga de los datos al almacén de datos y la actualización de los mercados de datos. Kimball en (Kimball, 2004) hace una extensión al proceso ETL considerándolo como el proceso mediante el cual se extraen, transforman, limpian, se conforman y se entregan los datos al almacén. 1.2.1.1 Proceso de Extracción La primera parte del proceso ETL consiste en extraer los datos desde los sistemas de origen. La mayoría de los proyectos de almacenamiento de datos fusionan datos provenientes de diferentes sistemas de origen. Cada sistema separado puede usar una organización diferente de los datos o formatos distintos. Los formatos de las fuentes normalmente se encuentran en bases de datos relacionales o ficheros planos, pero pueden incluir bases de datos no relacionales u otras estructuras diferentes. La extracción convierte los datos a un formato preparado para iniciar el proceso de transformación. Una parte intrínseca del proceso de extracción es la de analizar los datos extraídos, de lo que resulta un chequeo que verifica si los datos cumplen la pauta o estructura que se esperaba. De no ser así los datos son rechazados. Un requerimiento importante que se debe exigir a la tarea de extracción es que esta cause un impacto mínimo en el sistema origen. Si los datos a extraer son muchos, el sistema de origen se podría ralentizar e incluso colapsar, provocando que éste no pueda utilizarse con normalidad para su uso cotidiano. Por esta razón, en sistemas grandes las operaciones de extracción suelen programarse en horarios o días donde este impacto sea nulo o mínimo. (Wikipedia, 2012a) La lectura de datos del sistema operacional se puede extraer de dos formas,(Ing. Bernabeu R. Dario) durante la carga inicial (initial load) o durante el mantenimiento del almacén de datos (refreshment).. 13.

(23) Capítulo1: Aspectos teóricos del proceso de Integración de datos. Si los datos operacionales residen en un SGBD Relacional, el proceso de extracción se puede reducir a, por ejemplo, consultas en SQL o rutinas programadas. En cambio, si se encuentran en un sistema no convencional o fuentes externas, ya sean textuales, hipertextuales, hojas de cálculos, etc., la obtención de los mismos puede ser un tanto más dificultoso, debido a que, por ejemplo, se tendrán que realizar cambios de formato y/o volcado de información a partir de alguna herramienta específica y puede tener que realizarse a partir de informes o de datos proporcionados por los propietarios que deberán ser procesados posteriormente. Hay varias maneras de realizar la extracción, una de ellas es haciendo una notificación de actualizaciones, que depende de si el sistema fuente es capaz de proporcionar una notificación de que un registro ha sido cambiado, por ejemplo a través de uso de disparadores, y describir el cambio, esta es la manera más fácil de obtener los datos, pero en algunas fuentes como los ficheros planos, o archivos Excel no se puede lograr; en casos como esos la extracción se realiza de forma incremental, pero si identifican los registros que han sido modificados y es posible realizar la extracción de dichos registros, o por último, se puede hacer una extracción completa. La extracción completa requiere mantener una copia de la operación anterior de extracción, en el mismo formato para poder identificar los cambios. Algunos métodos de extracción de datos identifican los datos operacionales (relevantes) que han sufrido una modificación desde la fecha del último mantenimiento y aplican la comparación de instancias de la base de datos operacional. Para la identificación de los cambios se pueden usar marcas de tiempo (time stamping) en los registros, disparadores o fichero de log (gestión de transacciones) en el sistema operacional o una combinación de estas técnicas. Una vez que los datos son extraídos con la calidad requerida se les lleva a un segundo proceso llamado Transformación, donde se le aplican una serie de reglas de negocio o funciones que los preparan adecuadamente para el posterior proceso de carga. Después de la extracción un paso no menos importante debe ser realizado, la transportación (Alkis Simitsis National Technical University of Athens et al., 2005) para la cual pueden. 14.

(24) Capítulo1: Aspectos teóricos del proceso de Integración de datos. emplearse las tareas de codificación/decodificación, compresión/descompresión, los FTP y otras. 1.2.1.2 Proceso de Transformación Esta función es la encargada de convertir aquellos datos inconsistentes en un conjunto de datos compatibles y congruentes, para que puedan ser cargados en el almacén de datos. (Ing. Bernabeu R. Dario). Las principales transformaciones ocurren en el DSA. Las transformaciones pueden clasificarse según (java.net) en dos tipos fundamentales, las que ocurren a nivel de esquema y las que ocurren a nivel de los datos. Entre las que ocurren a nivel de esquemas son significativos los conflictos de nombres, los que se pueden presentar como homónimos (un mismo nombre es usado paa representar objetos diferentes) o sinónimos (un mismo objeto es representado por diferentes nombres), y los conflictos estucturales que están dados porque en las diferentes fuentes un mismo objeto es representado de formas distintas. En el nivel de los datos son muchas las transformaciones que pueden tener lugar debido a que la lista de problemas que se pueden presentar a este nivel es mucho más variada, entre ellas se pueden señalar la aparición de registros duplicados, diferente representacion de un valor, interpretación diferente de un mismo valor, y otros problemas técnicos de bajo nivel como la conversión de tipos de datos, apliación de máscara de formatos, etc. 1.2.1.3 Limpieza de datos La limpieza de datos (data cleansing, data scrubbing), es el proceso de descubrimiento, corrección o eliminación de datos erróneos de una base de datos (López-Porrero, 2011). El proceso de limpieza de datos permite identificar datos incompletos, incorrectos, inexactos, no pertinentes, etc. y luego substituir, modificar o eliminar estos datos sucios (data dirty). Después de la limpieza, la base de datos podrá ser compatible con otras bases de datos similares en el sistema. Los almacenes de datos constantemente están cargando y refrescando cientos de datos de diferentes fuentes, por lo que la probabilidad de que algunas de las fuentes contengan datos sucios es alta. Además, como los almacenes de datos se usan en sistemas de ayuda a la toma de decisiones, la corrección de estos es vital para evitar llegar a conclusiones erróneas. 15.

(25) Capítulo1: Aspectos teóricos del proceso de Integración de datos. (López-Porrero, 2011). Por ejemplo, información duplicada o ausente podría producir estadísticas incorrectas; debido a esto, la limpieza de datos se considera uno de los mayores problemas en los almacenes de datos. Las siguientes tareas describen en (Rahm and Do, 2000) el proceso de limpieza dentro del ETL:  Análisis de datos: Tarea para detectar los tipos de errores e inconsistencias que deben ser eliminadas.  Determinación del flujo de trabajo para las transformaciones y las reglas de correspondencia: Esto dependerá del número de fuentes de datos, su grado de heterogeneidad y la suciedad de los datos en ellas, de acuerdo con esto será el número de pasos de transformaciones y limpieza que serán ejecutados.  Verificación: El grado de corrección y efectividad del flujo de trabajo de las transformaciones y de las definiciones de las transformaciones deberá ser probado y evaluado.  Transformación: La realización de los pasos de la transformación ya sea por la ejecución del flujo de trabajo del ETL para la carga o durante la ejecución de solicitudes a las diversas fuentes.  Flujo inverso de los datos limpios: Después de que los errores se eliminan, el dato limpio debe reemplazar al sucio en las fuentes para así mejorar su calidad y evitar rehacer el trabajo de limpieza en futuros procesos de extracción. En ETL, la limpieza de datos es calificada como uno de los pasos que añade valor a los datos. La limpieza de datos es un proceso iterativo que implica la exploración y la interacción, es decir, puede definirse en un marco de trabajo que incluya una colección de métodos para la detección y la eliminación de errores, además de la revisión de los datos. Este puede estar integrado con otras etapas informáticas como la integración y el mantenimiento (Wikipedia, 2012b).. 16.

(26) Capítulo1: Aspectos teóricos del proceso de Integración de datos. En la siguiente tabla se muestran algunos de los tratamientos básicos al encontrar errores en los datos de las fuentes orígenes:(Ing. Bernabeu R. Dario) Posibles acciones típica al encontrarlos Errores encontrados     Datos Anómalos (Outliers).  .    . Datos Faltantes (Missing Values).  . Ignorarlos. Eliminar la columna. Filtrar la columna. Filtrar la fila errónea, ya que a veces su origen se debe a casos especiales. Reemplazar el valor. Discretizar los valores de las columnas. Por ejemplo de 1 a 2, poner “bajo”; de 3 a 7, “óptimo”; de 8 a 10, “alto”. Para que los outliers caigan en “bajo” o en “alto” sin mayores problemas. Ignorarlos. Eliminar la columna. Filtrar la columna. Filtrar la fila errónea, ya que a veces su origen se debe a casos especiales. Reemplazar el valor. Esperar hasta que los datos faltantes estén disponibles.. Un aspecto muy importante que se debe tener en cuenta al elegir alguna acción, es identificar el porqué de la anomalía, para luego actuar en consecuencia, con el fin de evitar que se repitan, agregándole de esta manera más valor a los datos de la organización. Una vez terminadas las transformaciones pertinentes con el fin de unificar los datos, se procede a la última etapa, llamada Carga, donde se exigen, para asegurar la calidad de los datos, ciertos requisitos antes de guardarlos en el sistema destino. 1.2.1.4 Proceso de Carga La fase de carga es el momento en el cual los datos resultantes de la fase anterior (Transformación) son cargados en el sistema de destino. Dependiendo de los requerimientos de la organización, este proceso puede abarcar una amplia variedad de acciones diferentes. En algunas bases de datos se sobrescribe la información antigua con nuevos datos. En los almacenes de datos se mantiene un historial de los registros de manera. 17.

(27) Capítulo1: Aspectos teóricos del proceso de Integración de datos. que se pueda hacer una auditoría y disponer de un rastro de toda la historia de un valor a lo largo del tiempo. Esta función se encarga, por un lado, de realizar las tareas relacionadas con la carga inicial, la cual se refiere precisamente a la primera carga de datos que se le realizará al almacén de datos, por lo general, esta tarea consume bastante tiempo, ya que se deben insertar registros que han sido generados aproximadamente, y en casos ideales, durante más de cinco años, por otro lado se puede hacer la actualización o mantenimiento periódico (siempre teniendo en cuenta un intervalo de tiempo predefinido para tal operación), ya que los mismos mueven pequeños volúmenes de datos, y su frecuencia está dada en función de la granularidad del almacén de datos y los requerimientos de los usuarios. El objetivo de esta tarea es añadir al depósito aquellos datos nuevos que se fueron generando después del último refrescamiento. Existe siempre la posibilidad de cargar el almacén de datos desde cero, este proceso se denomina carga total. De manera que ingresarán al almacén de datos, para su carga y/o actualización aquellos datos que han sido transformados y que residen en el almacenamiento intermedio, o aquellos datos de los OLTP que tienen correspondencia directa con la estructura del almacén de datos. Existen dos formas básicas de desarrollar el proceso de Carga (Leroot, 2009): la primera es la denominada acumulación simple, la cual consiste en realizar un resumen de todas las transacciones comprendidas en el período de tiempo seleccionado y transportar el resultado como una única transacción hacia el almacén de datos; almacenando siempre un valor calculado que expresa el promedio de la magnitud considerada. La segunda forma de realizar el proceso de carga es el rolling que se aplica en los casos en los cuales se almacena información resumida a distintos niveles, correspondientes a distintas agrupaciones de la unidad de tiempo o diferentes niveles jerárquicos, en alguna o varias de las dimensiones de la magnitud almacenada, por ejemplo, totales diarios, totales semanales, etc.. 18.

(28) Capítulo1: Aspectos teóricos del proceso de Integración de datos. La fase de carga interactúa directamente con la base de datos de destino. Al realizar esta operación se aplicarán todas las restricciones y disparadores que se hayan definido en esta (por ejemplo, valores únicos, integridad referencial, campos obligatorios, rangos de valores). Estas restricciones y disparadores (si están bien definidos) contribuyen a que se garantice la calidad de los datos en el proceso ETL, y deben ser tomados en cuenta. También se presentan algunos problemas que pueden ser evitados o tratados posteriormente, por ejemplo, cuando deben ser cargados grandes volúmenes de datos para ser procesados y/o transformados, o cuando se van a construir tablas resumidas, estas cargas deben realizarse en “ventanas” de carga pequeñas (usualmente una noche) cuando el almacén de datos está fuera de línea y permitir al administrador del sistema monitorear su estado, cancelar, suspender, resumir la carga, o cambiar las razones de la carga para posibilitar la recuperación después de las fallas sin pérdida de integridad. Sin embargo a pesar de lo tratado anteriormente, deben tenerse en cuentas algunas consideraciones antes de llegar a este proceso, por ejemplo:  planificar la secuencia de carga cuidadosamente como tablas seguras, puede ser necesario cargar primero algunas tablas, para ayudar a asegurar la carga de otras tablas posteriormente.  cargar las tablas de dimensiones antes de las tablas de hechos.  identificar algunas estrategias para el manejo de datos que llegan tarde y de valores inferidos.  realizar las cargas iniciales incrementales (por ejemplo, cargar un mes a la vez, o un tamaño de archivo determinado y así sucesivamente).  implementarse la integridad referencial reconstruyéndose con las llaves subrogadas. (Dario, 2010) Otro paso posterior a la carga, es el llamado proceso de indexación. La indexación puede implementarse de dos formas: durante la carga, con el índice habilitado o un proceso tupla a tupla (lento) o una indexación después de la carga, cargándolo con el índice deshabilitado o una creación del índice total o parcial (rápido).. 19.

(29) Capítulo1: Aspectos teóricos del proceso de Integración de datos. Para implementar los índices se usan tablas hash, aunque también pueden usarse otras estructuras como los B-trees y R-trees. 1.2.2 Procesamiento paralelo en sistemas ETL El proceso de ETL, en un solo hilo, puede ser eficiente para algunos casos en que la cantidad de datos sea pequeña, pero cuando estos datos crecen exponencialmente, se hace necesario implementar una solución que permita realizar varias tareas a la vez, lo que se conoce como procesamiento paralelo. Un desarrollo reciente en el software ETL es la aplicación del procesamiento paralelo, lo que ha permitido utilizar una serie de métodos para mejorar el rendimiento general de los procesos ETL cuando se trata de grandes volúmenes de datos. Hay 3 tipos principales de paralelismos que se pueden implementar en las aplicaciones ETL:  De datos: Consiste en dividir un único archivo secuencial en pequeños archivos de datos para proporcionar acceso paralelo.  De segmentación (pipeline): Permitir el funcionamiento simultáneo de varios componentes en el mismo flujo de datos. Por ejemplo, buscar un valor en el registro número 1 a la vez que se suman dos campos en el registro número 2.  De componente: Consiste en el funcionamiento simultáneo de múltiples procesos en diferentes flujos de datos, pertenecientes todos ellos a un único flujo de trabajo. Esto es posible cuando existen porciones dentro de un flujo de trabajo que son totalmente independientes entre ellas a nivel de flujo de datos. Estos tres tipos de paralelismo no son excluyentes, sino que pueden ser combinados para realizar una misma operación de ETL. Una dificultad adicional es asegurar que los datos que se cargan sean relativamente consistentes. Las múltiples bases de datos de origen tienen diferentes ciclos de actualización (algunas pueden ser actualizadas cada pocos minutos, mientras que otras pueden tardar días o semanas). En un sistema de ETL será necesario que se puedan detener ciertos datos hasta que todas las fuentes estén sincronizadas. Del mismo modo, cuando un. 20.

(30) Capítulo1: Aspectos teóricos del proceso de Integración de datos. almacén de datos tiene que ser actualizado con los contenidos en un sistema de origen, es necesario establecer puntos de sincronización y de actualización. Tampoco es recomendable usar muchos procesos en paralelo, pues el tiempo de procesamiento aumentaría en cuanto a procesador, pero caería en el llamado cuello de botella con la Entrada/Salida del disco, debido a que los procesos están leyendo simultáneamente del mismo disco. Una solución aconsejable en este caso es dispersar los archivos en discos y controladoras independientes o pasar a una tecnología de disco más rápida. (Carroll) Los procesos ETL pueden ser muy complejos. Un sistema ETL mal diseñado puede provocar importantes problemas operativos, por lo que, por esta y otras muchas razones deben enfrentarse varios retos. 1.2.3 Desafíos actuales en el proceso de ETL En un sistema operacional el rango de valores de los datos o la calidad de estos pueden no coincidir con las expectativas de los diseñadores a la hora de especificarse las reglas de validación o transformación. Es recomendable realizar un examen completo de la validez de los datos (Data profiling) del sistema de origen. Durante el análisis se identificarán las condiciones necesarias para que los datos puedan ser tratados adecuadamente por las reglas de transformación especificadas. Esto conducirá a una modificación de las reglas de validación implementadas en el proceso ETL. Los almacenes de datos son casi siempre alimentados de manera asincrónica desde distintas fuentes, que sirven a propósitos muy diferentes. El proceso ETL es clave para lograr que los datos extraídos asincrónicamente de orígenes heterogéneos se integren en un entorno homogéneo. (Wikipedia, 2012a) La escalabilidad de un sistema de ETL durante su vida útil tiene que ser establecida durante el análisis. Esto incluye la comprensión de los volúmenes de datos que tendrán que ser procesados según los acuerdos de nivel de servicio (SLA: Service Level Agreement). El tiempo disponible para realizar la extracción de los sistemas de origen podría cambiar, lo que implicaría que la misma cantidad de datos tendría que ser procesada en menos tiempo.. 21.

(31) Capítulo1: Aspectos teóricos del proceso de Integración de datos. Algunos sistemas ETL son escalables para procesar varios terabytes de datos para actualizar un almacén de datos que puede contener decenas de terabytes de datos. El aumento de los volúmenes de datos que pueden requerir estos sistemas pueden hacer que los lotes que se procesaban a diario pasen a procesarse en micro-lotes (varios al día) o incluso a la integración con colas de mensajes o a la captura de datos modificados (CDC: Change Data Capture) en tiempo real para una transformación y actualización continua. 1.3 Conclusiones parciales. Las herramientas de Integración de datos combinan datos de varias fuentes diferentes para proporcionar a los usuarios una vista unificada de los datos, de modo que son sumamente importantes en el proceso de toma de decisiones dentro de la inteligencia empresarial. Las herramientas de integración de datos de forma general deberán dar soporte para:  la conectividad con disimiles fuentes sobre distintas plataformas,  el uso y mantenimiento de los metadatos,  la migración de datos,  la sincronización de datos y  la construcción de almacenes de datos. El proceso de ETL, donde la integración de datos es imprescindible, es la pieza de software responsable de la extracción de los datos desde algunas fuentes, su limpieza, personalización e inserción en un almacén de datos. El desarrollo de un sistema de ETL es un proyecto complejo que demanda un plan previo para enfrentar su elaboración, a partir de los requerimientos impuestos por las necesidades del negocio.. 22.

(32) Capítulo2: Presentación del SSIS. CAPÍTULO 2. PRESENTACIÓN DEL SSIS. 23.

(33) Capítulo2: Presentación del SSIS. CAPÍTULO 2. PRESENTACIÓN DEL SSIS En este capítulo se describe el servicio de integración de datos del SQL Server 2008 R2 (SSIS por sus siglas en inglés), definiendo todos los elementos relacionados con una solución de integración. 2.1 Servicio de integración de datos del SQL Server El SSIS es una plataforma que reúne un motor y un ambiente de diseño para la creación de soluciones empresariales de transformación e integración de datos que da solución a disímiles y complejas tareas, desde una simple importación o exportación de datos hasta una solución completa de un sistema ETL, requerido en cualquier escenario de inteligencia de negocios. Esta herramienta ofrece un ambiente de diseño gráfico muy intuitivo en el que se pueden crear complejos programas que resuelvan tareas de mantenimiento de las bases de datos, de comunicación con otros sistemas, como por ejemplo Servidores FTP o servicios de la Web que lean y escriban en ficheros XML e incluso resolver todo esto como parte de una única solución. A diferencia de otros sistemas de ETL que se focalizan en el movimiento de los datos, hacia el área intermedia de datos, lugar donde se realizan las transformaciones, el servicio de integración del SQL Server realiza las transformaciones de los datos a su forma final dentro del propio proceso de extracción. 2.2 Usos típicos del servicio de integración de datos El servicio de integración de datos sirve para realizar una gran variedad de procesos de migración, entre sus usos típicos se destacan:  Combinar datos desde fuentes de datos heterogéneos. Los datos suelen almacenarse en muchos formatos de almacenamiento distintos, por lo que extraer datos de todos los orígenes y combinarlos en un solo conjunto de datos coherente constituye un desafío. Esta situación puede producirse por diversas razones. Por ejemplo:. 24.

(34) Capítulo2: Presentación del SSIS.  Muchas organizaciones archivan información que está almacenada en sistemas de almacenamiento de datos antiguos. Estos datos pueden no ser importantes para las operaciones diarias, pero pueden resultar útiles para el análisis de tendencias, que requiere datos recopilados a lo largo de un período prolongado de tiempo.  Las sucursales de una organización pueden usar distintas tecnologías de almacenamiento de datos para almacenar los datos operativos. Es posible que el paquete tenga que extraer datos de hojas de cálculo y de bases de datos relacionales para poder combinar los datos.  Los datos pueden estar almacenados en bases de datos que usan distintos esquemas para los mismos datos. Es posible que el paquete tenga que cambiar el tipo de datos de una columna o combinar datos de varias columnas en una sola columna para poder combinar los datos.  El servicio de integración de datos puede conectarse a una gran variedad de orígenes de datos que incluyen desde ficheros planos hasta bases de datos relacionales. Una vez que se han combinado los datos de los diferentes orígenes, aplicando transformaciones que los expresan en formatos compatibles, estos son cargados a uno o varios destinos.  Poblar almacenes y mercados de datos. Los datos de los almacenes y mercados de datos suelen actualizarse frecuentemente y normalmente las cargas de datos son muy grandes. Mediante este servicio se pueden cargar las tablas de dimensiones y hechos en la base de datos. Si los datos de origen de una tabla de dimensión están almacenados en varias fuentes, estas se combinarán en un solo conjunto de datos y se podrá realizar la carga a la tabla de la dimensión en un solo proceso, en lugar de utilizar un proceso independiente para cada fuente. La actualización de datos de almacenamientos de datos y mercados de datos puede ser compleja, ya que en ambos se suelen incluir dimensiones lentamente cambiantes que pueden ser difíciles de administrar mediante un proceso de transformación de datos. El. 25.

(35) Capítulo2: Presentación del SSIS. servicio de integración de datos brinda un asistente que automatiza la compatibilidad para estas dimensiones. En el proceso de carga a un almacén o mercado de datos es muy frecuente que se necesite consolidar la información de las fuentes, para ello el servicio de integración permite calcular funciones tales como SUM, AVERAGE y COUNT.  Limpiar y normalizar datos. Independientemente de si los datos se van a cargar en una base de datos de procesamiento de transacciones en línea o de procesamiento analítico en línea, una hoja de cálculo o un archivo, hay que limpiarlos y normalizarlos antes de cargarlos. Puede ser necesario actualizar los datos porque los datos proceden de varias fuentes y en cada una se usan convenciones y estándares distintos. Con el SSIS los datos pueden limpiarse y normalizarse realizando operaciones para cambiar las mayúsculas y minúsculas de los datos, convertir datos a un tipo o formato distinto, crear nuevos valores de columna basados en expresiones, concatenar columnas, también puede limpiar datos reemplazando los valores de las columnas por valores de una tabla de referencia mediante una búsqueda exacta o aproximada y eliminando los registros duplicados.  Automatizar las funciones administrativas y la carga de datos. En un entorno de bases de datos se hace necesario automatizar las funciones administrativas como la copia de seguridad y la restauración de bases de datos, la copia de bases de datos de SQL Server y los objetos que contienen, la copia de objetos de SQL Server y la carga de datos. SSIS pueden realizar estas funciones. Además facilita la copia de objetos de bases de datos de SQL Server como tablas, vistas y procedimientos almacenados, bases de datos, inicios de sesión, estadísticas y agregar, modificar y eliminar objetos y datos de SQL Server mediante instrucciones TransactSQL.. 26.

(36) Capítulo2: Presentación del SSIS. 2.3 Arquitectura del servicio de integración de datos. El SSIS incluye diversos componentes, los que se muestran en la siguiente figura:. De estos, los más importantes para utilizar SSIS correctamente son:  El diseñador. El diseñador es una herramienta gráfica que se puede usar para crear y mantener soluciones. El Diseñador SSIS está disponible en Business Intelligence Development Studio como parte de un proyecto.  Motor de ejecución. El motor de ejecución del SSIS guarda el diseño de las soluciones, las ejecuta y brinda soporte para la autentificación, puntos de interrupción, configuración, conexiones y transacciones.. 27.

(37) Capítulo2: Presentación del SSIS.  Tareas y otros ejecutables. El SSIS incluye como ficheros ejecutables varios objetos: los paquetes, los contenedores, las tareas, los controladores de eventos y otras tareas desarrollados por los usuarios.  El Motor de flujo de datos y los componentes del flujo de datos.. La tarea de flujo de datos encapsula el motor de flujo de datos. El motor de flujo de datos proporciona los búferes en memoria que mueven datos desde el origen hasta el destino y llama a las fuentes que extraen los datos desde archivos y bases de datos relacionales. El motor de flujo de datos también administra las transformaciones que modifican los datos y los destinos que cargan datos o los ponen a disposición de otros procesos. Los componentes de un flujo de datos son los orígenes, transformaciones y destinos que se incluyen en el SSIS, pudiéndose añadir otros componentes personalizados en un flujo de datos.  Las interfaces de programación de aplicaciones (API) o modelo de objetos.. El modelo de objetos de SSIS incluye las API para crear componentes personalizados para su uso en paquetes, o aplicaciones personalizadas que crean, cargan, ejecutan y administran paquetes. El programador puede escribir aplicaciones, tareas o transformaciones personalizadas utilizando cualquier lenguaje del tipo Common Language Runtime (CLR).  Servicio de integración. El servicio de integración permite usar SQL Server Management Studio para supervisar paquetes en ejecución y para administrar el almacenamiento de los paquetes.  Asistente para importación y exportación de SQL Server El Asistente para importación y exportación de SQL Server puede copiar datos entre orígenes de datos para los que esté disponible un proveedor de datos de .NET Framework o un proveedor OLE DB nativo. El Asistente ofrece también el método más simple para crear paquetes que copien datos de un origen en un destino. Existen importantes herramientas complementarias para lograr ejecutar las tareas de integración: - DTEXEC ejecuta los paquetes usando la línea de comando. Muy útil para automatizar tareas.. 28.

(38) Capítulo2: Presentación del SSIS. - DTUTIL administrar los paquetes. Permitiendo realizar copias, borrar paquetes, entre otras tareas de administración. 2.4 Arquitectura de un paquete de SSIS. Para explicar la estructura de un paquete se usará el ejemplo llamado Data Cleaning que se incluye en la propia herramienta, el cual hace chequeos de los datos y los limpia. El proyecto cuenta con un paquete llamado DataCleaning.dtsx que usa datos de una lista de nombres y direcciones que representan clientes potenciales. Estos datos requieren limpieza, pues contienen incontables errores, información perdida, clientes ya existentes en la base de datos, clientes incorrectos y múltiples instancias con pequeñas diferencias del mismo cliente. En la siguiente tabla se describen las tablas de salida: Tabla ExistingCustomerLeads. Descripción Contiene las filas que coincidieron exactamente y las que coincidieron en un alto grado de similitud con los clientes existentes. NewCustomerLeads. Contiene las filas que no coincidieron con los clientes existentes. Si la lista contiene múltiples instancias de del mismo nombre o una alta similitud de un nombre en particular, solo una fila será dirigida a la tabla NewCustomerLead, y los duplicados serán dirigidos a la tabla DuplicateCustomerLeads. DuplicateCustomerLeads Contiene los duplicados de los nuevos clientes 2.4.1 El propio paquete. De modo general, para obtener una solución, se necesita uno o varios proyectos, cada proyecto a su vez, puede estar formado por uno o varios paquetes. El paquete, en sí mismo, constituye el núcleo central del SSIS y no es más que una colección organizada de conexiones, elementos de flujo de control, elementos de flujo de datos, controladores de eventos, variables y configuraciones que se pueden ensamblar con la ayuda de las herramientas gráficas de diseño proporcionadas por SSIS o mediante. 29.

(39) Capítulo2: Presentación del SSIS. programación. Una vez completado el paquete, se guarda en SQL Server, en el almacén de paquetes SSIS o en el sistema de archivos. El paquete es la unidad de trabajo que se recupera, ejecuta y guarda. El siguiente diagrama muestra un paquete genérico que contiene un flujo de control con una tarea Flujo de datos que, a su vez, contiene un flujo de datos.. Una vez creado el paquete básico, puede agregársele otras características avanzadas para extender su funcionalidad y puede configurarse estableciendo propiedades a nivel de paquete que permiten implementar la seguridad, reiniciar paquetes desde puntos de rupturas e incorporar transacciones en el flujo de trabajo del paquete. 2.4.2 Administradores de conexiones. Los paquetes utilizan conexiones para realizar diferentes tareas y para implementar características, SSIS utiliza los administradores de conexiones para ello. En el ejemplo Data Cleaning existen dos conexiones:  Un administrador de conexiones de archivo llamado CreateTables.sql que se conecta con el archivo que contiene el código SQL que se usará en el paquete.  Un administrador de conexiones OLE DB, llamado (local).AdventureWorks que se conecta con la base de datos AdventureWorks que está creada localmente en el equipo.. 30.

(40) Capítulo2: Presentación del SSIS. Sobre esta base datos se harán chequeos de existencia, de los registros de acuerdo a la referencia y se crearán tablas de trabajo temporales que requieren algunas transformaciones para hacer el trabajo. Al mismo tiempo, SSIS ofrece otras posibilidades de conexiones: . Conectar con orígenes y destinos de datos tales como archivos de texto, archivos XML, libros de Excel y bases de datos relacionales para extraer y cargar datos.. . Conectar con bases de datos relacionales que contienen datos de referencia para realizar búsquedas exactas o aproximadas.. . Conectar con bases de datos relacionales para ejecutar instrucciones SQL, tales como los comandos SELECT, DELETE e INSERT, así como procedimientos almacenados.. . Conectar con SQL Server para realizar tareas de transferencia y mantenimiento, tales como la realización de copias de seguridad de bases de datos y la transferencia de inicios de sesión.. . Escribir entradas del registro en archivos XML, de texto y tablas de SQL Server y configuraciones de paquete en tablas de SQL Server.. . Conectar con bases de datos y proyectos de Analysis Services para tener acceso a modelos de minería de datos, procesar cubos y dimensiones, y ejecutar el código del lenguaje de definición de datos.. . Especificar carpetas y archivos existentes o crear carpetas y archivos nuevos para utilizar en tareas y ciclos Foreach.. SSIS usa el administrador de conexiones como una representación lógica de una conexión. En tiempo de diseño, se establecen las propiedades del administrador de conexiones para que describa la conexión física que crea cuando se ejecuta el paquete. Un administrador de conexiones incluye la propiedad ConnectionString que se establece en la etapa diseño. En. 31.

(41) Capítulo2: Presentación del SSIS. el tiempo de ejecución, se crea una conexión física mediante el valor de la propiedad de la cadena de conexión. Un paquete puede usar varias instancias de un tipo de administrador de conexiones y se pueden establecer las propiedades de cada instancia. En el tiempo de ejecución, cada instancia de un tipo de administrador de conexiones crea una conexión que tiene diferentes atributos. SSIS proporciona diferentes tipos de administradores de conexión que permiten que los paquetes se conecten a una serie de orígenes de datos y servidores, los cuales se instalan cuando el servicio es instalado siendo posible que los usuarios puedan crear otros administradores de conexión. En la tabla siguiente se indican los tipos de administradores de conexión proporcionados por SSIS. Tipo. Descripción. ADO. Se conecta a los objetos de Objetos de datos ActiveX (ADO).. ADO.NET. Se conecta a un origen de datos mediante un proveedor .NET.. CACHE. Lee los datos del flujo de datos o de un archivo caché (.caw) y puede guardar los datos en el archivo caché.. EXCEL FILE FLATFILE. Se conecta a un archivo de libro de Excel. Se conecta a un archivo o carpeta. Se conecta a los datos en un solo archivo plano.. FTP. Se conecta a un servidor FTP.. HTTP. Se conecta a un servidor web.. MSMQ. Se conecta a una cola de mensajes.. MSOLAP100. Se conecta a una instancia de SQL Server Analysis Services o a un proyecto de Analysis Services.. MULTIFILE MULTIFLATFILE OLEDB ODBC. Se conecta a varios archivos y carpetas. Se conecta a carpetas de datos con archivos planos. Se conecta a un origen de datos mediante un proveedor OLE DB. Se conecta a un origen de datos mediante ODBC.. 32.