Guia Practica de Integration Services

Que es Integration Services?

Integration Services es parte de SQL Server,  es una herramienta para tareas de migración de datos, integración de datos,  o crear flujos de datos entre aplicaciones.

Es considerada una de las principales herramientas de ETL.

Que es ETL?

Extraer, Transformar y Cargar

Extraer

-          Traer datos de una fuente de datos

-          Se usan DataSources, Data Sources Views,  y Administrador de Conexiones

Transformar

-          Se manipulan datos y se mueven a un destino

-          Limpiarlos, formatearlos, transformarlos

-          Puede ser simple o complejo como sea necesario

Cargar

-          Insertar datos a un Destino,  archivos de Texo, Base datos, Hojas de Excel o bien a un Datawarehouse.

Cuando usarlo?

Principalmente para cargar un Datawarehouse.   Nos permite integrar datos,  extraer de 1 o mucha fuentes,  transformarlos y cargarlos a un destino.

Ejemplo: Realizar tareas con archivos.

   Obtener información y enviarlos por correo. 

  Mantenimiento de la Base datos

Paquetes de Integration Services

La unidad fundamental de ejecución.  Tiene un extensión de archivo DTSX.  Dentro del paquete esta la lógica de lo que quiero implementar.  Es un archivo XML que se ejecuta con la herramienta de SSIS e interpreta lo que se va a realizar.  Una solución consiste en uno o más paquetes

Típicamente consisten en muchos componentes.

-          Control Flows

o   Containers  -- Loop, o la operación de sequencia

o   Taks  -- Describe los pasos para habilitar pasos de la lógica

o   Precedence Constraints --  Establece el orden de la operación, quien precede a a quien  y las condiciones si las se completan la tarea o no

-          Data Flows

o   Es el Core de la funcionalidad de los datos

-          Connection Managers

o   Representa las fuentes de datos de origen o destino

Herramientas

-          Import and Export Wizard  (DTSWizard)

-          Business Intelligence Development Studio (BIDS)

-          SQL Server Management Studio

-          Notepad

-          DTEXEC desde CMD

-          SQL Server Agent

Ejercicio 1 – Nuestro Primer Paquete

1.       Abrir el DTSWizard desde el menú de Inicio o bien Ejecutar DTSWizard

2.       Conectarse a Northwind

3.       Luego Seleccionar Flat File Destination

4.       Hacer un Pre-View de los datos y de los tipos de datos.

5.       Guardar el paquete Fisicamente y Ejecutarlo Posteriormente

6.       Abrir el paquete y explorarlo.  Adicionalmente cambiar la ruta del Archivo.

7.       Abrir el paquete desde Notepad.

8.       Abrir el paquete desde BIDS

NOTA: Recordar que un paquete no es un EXE sino un documento que se carga siempre y cuando tengamos SSIS instalado.

Ejercicio 2  -  Conociendo BIDS

Diferencias entre Control Flow y DataFlow

ControlFlow : Permite crear la funcionalidad de Flujo de Datos,  en que orden, en que secuencia, preparación del ambiente de Trabajo

DataFlow : Trabaja con los datos en sí. 

1.       Crear un DataFlow y verificar la información de DataFlow

2.       Crear otro DataFlow y verificar la información.

Por tanto todo paquete tiene un ControlFlow y puede contener o no un Dataflow

Task Versus Components

Tareas dentro de ControlFlow

1.       Agregar un Sequence Container

2.       Organizar los DataFlow dentro del Sequence

3.       Agregar una Tarea de Email

4.       Conectar el Sequence con el Email con los Constraints

5.       Agregar componentes

a.       Sources  ---   OLE DB Source

b.      Transformations  -- Derived Column

c.       Destinations  -- Excel Destination

    Event Handler

1.       Crear un Evento OnError para el paquete

2.       Crear un Execute Task en el Evento OnError  y luego enviar un Email

Package Explorer

Navegar en el Paquete.

Creando Variables

Las variables se crean en dependencia de Scope (Ámbito).    Una variable a Nivel del Paquete puede ser accedida por los componentes existentes en el.

Crear una variable para el paquete y una para el DataFlow.

Ejercicio 3 -  Paquete Sencillo

1.       Crear un nuevo paquete llamado CargaDatos

2.       Desde el DataFlow crear un OLEDB Source

3.       Utilizar Derived Column y Unir 2 Columnas UPPER ([Col1] + “ ” + [Col2])   y agregarlo como nueva columna

4.       Crear un DataReaderDestination

5.       Agregar vistas para verificar la transferencia de los Datos.

Ejercicio 4 – Generando Archivos de Texto

1.       Crear Nuevo Proyecto llamado ManejoArchivos

2.       Desde Northwind cargar Products  y exportarlos a un archivo CSV

3.       Primero Agregar Un DataFlow

4.       Desde el DataFlow crear un OLEDB Source

5.       Dirigirla hacia un Flat File Destination

6.       Configurar origen y destino y ejecutar el paquete.

7.       Verficar Archivo

8.       Crear un FTP Task y configurarlo con el FTP proporcionado por el Instructor y enviar el Archivo Creado.

Ejercicio 5 – Usando Transformaciones

1.       Crear nuevo paquete llamado Transformaciones

2.       Crear 3 Conexiones para cada uno de los Archivos de Texto Proporcionados. Reds, Silvers, Blacks.

3.       Crear un Union Transformation

4.       Crear un Sort Transformation y Ordenar por Name, Color

5.       Agregar Audit Transformation y Unirlo a Sort.  Agregar Nombre Paquete, Tiempo de Ejecución,  Nombre de la Maquina, Usuario, Nombre de Tarea

6.       Finalmente agregar un Excel Destination y ubicarlo en la Carpeta de Ejemplos.  Todos.xls La Hoja llamada Todos.

7.       Ejecutarlo y verificar el Archivo.

8.       Agregar MultiCast Transformation

9.       Ordenar el Flujo de Sort a MultiCast y de aquí a Audit y a Excel

10.   Agregar un nuevo destino OleDB Destination y almacenar la información del Log.

Ejercicio 6 -  Importando Archivos de Texto

1.       Crear un nuevo Paquete ImportarArchivos

2.       Crear una Nueva Base de Datos llamada SSIS_Demos

3.       Ejecutar Script para crear Tabla Customers

4.       Crear un Nuevo DataFlow Task llamado Load Customers.  NOTA:  Se podría cargar la información con un Bulk Insert Task pero no nos ofrece la capacidad de Tranformar nuestros datos.

5.       Crear Nuevo Flat File Source,  crear la conexión al Archivo de Clientes.  Llamarla Archivo Customers

6.       Quitar algunas Columnas que no se deberán Cargar:  RowID, Passwords.

7.       Crear un OLBDDestination y crear una nueva conexión

8.       Asignarle Origen Destino

9.       Agregar a la Tabla Customers la columna Genero de Tipo nvarchar(1)  ---  FechaCarga DateTime  ---  Usuario nvarchar(50)

10.   Agregar un Componente Derived Column y Crear una Nueva columna derivada basado en Title y asignarlo a Genero. [Title] == "Mr." ? "M" : [Title] == "Ms." ? "F" : "U"

11.   Asignarle la Funcion GetDate() y la Variable del Sistema User a las otras 2 Columnas.

12.   Ejecutar Nuestro Paquete y ver la Pestana Progress y verificar los Warnings.

13.   Regresar a Control Flow y agregar Execute SQL Task configurarla para hacer TRUNCATE TABLE Customers.

14.   Agregar ForEachContainer

15.   Crear variable en el ámbito del proyecto para que sea accesible CustomerPath de Tipo String.

16.   Configurar ForEachContainer para que escriba en la variable

17.   Editar Constraint del ForEachContainer al DataFlow con 2 Condiciones que se ejecute correctamente y que también cumpla la condición @CustomerPath ¡= “”

18.   Realizar pruebas y modificar nombre para verificar que no se ejecute el DataFlow de No encontrarla.

19.   Agregar File System Task,  Renombrar el Archivo CustomerLoaded La Conexión de Origen (Source) deberá ser la misma que se utilizo para cargar el Archivo. Y la Destino seria Nueva Conexión Nuevo Archivo. Accion Rename File.

20.   Agregar File System Task,   Moverlo a un Folder OUT. Conexion de Origen la Anterior, Conexion Destino Nueva Conexion Folder Existente. Accion Move File

21.   Verificar que se realice correctamente.

22.   Agregar un Execute SQL Task.  Verificar que si esta vacio el ForEachContainer ejecute la SQL Task y esta importe los datos a una Tabla de Log. Hay que crearla antes, poner Fecha y Hora de Ejecucion y un mensaje de no carga.Reiniciar el  Proceso.