Guia Practica de Integration Services - Intermedia

Ejercicio 1 - Constraints

1.    Crear un nuevo paquete llamado Constraints

2.    Agregar un Execute SQL Task,    configurarlo y establecer la siguiente setencia :  SELECT 1 / CAST(2*RAND() AS INT) que nos regresara un valor aleatorio 1 o 0

3.    Agregar 1 Script Task Correcto y crear un Contraint

4.    Ejecutar el Paquete, ver cómo se comporta.

5.    Agregar 1 Script Task llamado Fallo que se ejecute cuando Falle el Paquete

6.    Copiar el Execute SQL Task, y conectarlo al Script Task Fallo,  que se ejecute cuando el Execute SQL Task Falle

7.    La ejecución del Script Fallo seria únicamente cuando ambos Fallen.

8.    Ejecutar el paquete

9.    Editar los Constraint para que la sea “OR” cualquiera de los dos que fallen se ejecutaría el Script Task Fallo.

10. Ejecutar el paquete

Ejercicio 2 – Variables ControlFlow

1.       Crear un nuevo Paquete llamado VariablesControlFlow

2.       Agregar un ForEach Loop Container

3.       Crear una nueva variable a nivel de Paquete de tipo string llamada Nombre_Archivo

4.       Asignar la Variable al ForEach Container, configurarlo en Collection como For Each File Enumerator y seleccionar el folder donde se encuentran los Archivos .CSV

5.       Agregar un Script Task

6.       Asignar la variabla al Script Task como “Read”

7.       Editar el script haciendo script en Edit Script. VB.NET: MessageBox.Show(Dts.Variables("Archivo_Nombre").Value.ToString) C#: MessageBox.Show(Dts.Variables["Archivo_Nombre"].Value.ToString);

8.       Ejecutar el Paquete

Ejercicio 3 –Trabajando con Configuraciones

1.       Menu SSIS seleccionar Package Configuration

2.       Crear uno nuevo y almacenarlo en un XML,  crear un nuevo Archivo de Configuracion llamado MyConfiguration

3.       Almacenar propiedades de Folder y  File Spec

4.       Visualizar Documento XML y verlo con formato de documento.

5.       Crear una variable de Entorno. SET SSISRutaConfig = “C:\archivo.dtsconfig”,  Ver Listado de Variables SET

6.       En las configuraciones Editar la configuración anterior y en lugar de asignarle al Archivo asignárselo a la variable de Entorno.

Ejercicio 4 – Containers

En SSIS existen 5 tipos de Containers:

-          Task Host Container :  En cualquier tarea se agrega un Container

-          Sequence Container

-          For Loop Container

-          ForEach Loop Container

-          Grouping Container

1.       Crear un nuevo paquete llamado ForEach

2.       Agregar un For Each Container

3.       Agregar dentro este un DataFlow Task

4.       Configurar el For Each Container à For Each File Enumerator,  Seleccionar el Folder de los Archivos de Texto y tipos de archivo .CSV

5.       Crear una Nueva Variable a nivel de Paquete llamada txtArchivo de tipo string.

6.       Crear una nueva fuente de datos FlatFileSource, crear su conexión a un archivo de la carpeta de Archivos de Texto.

7.       Vamos a asignarle la conexión dinámica a cada archivo dentro de la carpeta configurada en el For Each Contaniner,  seleccionamos la conexión, propiedades y hacemos click en Expressions.

8.       Seleccionamos la propiedad ConnectionString y el valor la variable txtArchivo.

9.       Agregamos MultiCast Transformations

10.   Agregamos un Dataviewers

11.   Ejecutamos el Paquete, vemos como está conectado al primer archivo,  y luego le damos “Detach” y vemos los datos del siguiente archivo.

Ejercicio 5 -   Operaciones de Archivo

1.       Crear un Nuevo Paquete llamado ManejoArchivos

2.       Agregar un For Each Loop Container

3.       Agregamos un Data Flow – Cargar Archivo dentro del For Each Loop

4.       Agregamos 2 File Sytem Task  - Renombrar Archivo , Mover Archivo , dentro del For Each  y creamos la relaciones de precedencia.

5.       Configuramos  el For Each para que busque los archivos de una Carpeta llamada InBox donde se encuentran 3 archivos .CSV.  ForEach File Enumerator.

6.       Creamos una variable a Nivel del paquete llamada RutaArchivo de tipo String.

7.       Asignamos la variable al For Each en variable Mapping.

8.       Ingresamos al DataFlow, agregamos un Flat File Source, Derived Column,  OLE DB Destination.

9.       Configuramos el Flat File Source,  lee desde los archivos en InBox

10.   Configuramos la Conexión creada al Archivo, en propiedades, expressions, y asignamos  ConnectionString la variable.

11.   Configuramos la Derived Column, nueva columna, llamada Tipo, con la siguiente expresión: UPPER( [Color])== "BLACK" ? "B":UPPER( [Color])== "RED"?"R":UPPER( [Color])== "SILVER"?"S":"U"

Donde   “==” à SI,  “?” à Entonces,  “:” à Sino

12.   Además agregamos la Fecha de Ejecución y el Nombre Usuario como columnas derivadas adicionales.

13.   Configurar el File System Task Renombrar Archivo, la operación es Rename.  Nos pedirá 2 conexiones Source y Destination.   Para Source seleccionamos la variable “RutaArchivo” y le decimos  que el Path será de tipo variable.  Para destination del File System Task Renombrar Archivo, tendremos que crear una nueva variable ya que nuestra conexión es dinámica.  Creamos una variable “RutaDestino”

14.   Tendremos que editar la variable para que obtenga valores de la otra variable y cree otro archivo. En Propiedades de la Variable “RutaDestino”,  le decimos que se evalue como expresión, EvaluateExpression= True y luego en Expressions, vamos a renombrar el valor de la Variable “RutaArchivo” para que tenga un sufijo de tal modo:  “Archivo_Loaded.CSV”.

SUBSTRING( @[User::Variable] , 1,  LEN( @[User::Variable] )-4) + "_Loaded" + ".csv"

15.   Configurar el File System Task Mover Archivo,  la Source seria la variable “RutaDestino”, y el destination seria una nueva conexión a un Folder Existente OutBox

16.   Ejecutar el paquete.

17.   Crear una configuración para el paquete que almacene los valores para la Conexión del Folder de Destino.

Ejercicio 6 – For Loop Container

1.       Crear u nuevo paquete llamado ProcesarArchivo

2.       Crear una nueva variable llamada ArchivoCheck de tipo Int32 a nivel del paquete

3.       Agregar un For Loop Container

4.       Editarlo y en propiedades  InitExpresion Inicializamos la variable a 0 :   @[User::ArchivoCheck] = 0

5.       En EvalExpression @[User:: ArchivoCheck] = 0

6.       Agregamos un Script Task, en Variables de Escritura asignamos la variable,  y hacemos click en Script

7.       Dentro del Editor verificamos si existe un Archivo de tipo Texto, utilizando el nombre de espacio system.IO.File.Exists,  si existe asigamos el valor a la variable a 1.

If (System.IO.File.Exists("c:\NewFile.txt")) then

            Dts.Variables("User::ArchivoCheck").Value = 1

   End If

8.       Agregamos otro Script Task y lo configuramos para q una vez que se complete el For Loop envie un mensaje “Archivo Recibido”.  Le Asignamos la variable ArchivoCheck como lectura

MsgBox("Archivo Recibido")

9.       Creamos un Constraint del Container a este Script

10.   Ejecutamos el paquete, este iterara hasta encontrar el archivo.

11.   Agruparlo, click derecho group.

Ejercicio 7 – Usando Transacciones

1.       Crear un Nuevo paquete llamado Transacciones

2.       Agregar 3 Execute SQL Task,  renombrarlos:  Crear Tabla Contabilidad,  Agregar Debitos,  Agregar Creditos

3.       Configurar la Crear Tabla Contabilidad,  una nueva conexión a nuestra base de ejemplos.  Input Directly y creamos nuestra tabla con los siguientes campos:  Cuenta varchar(10) , Monto money, Movimiento varchar(1)

4.       Configuramos Agregar Debitos,   y hacemos la sentencia T-SQL  para insertar  Cuenta:  A,  Monto: 100,  Movimiento : “D”

5.       Configuramos Agregar Debitos,   y hacemos la sentencia T-SQL  para insertar  Cuenta:  B,  Monto: 100G,  Movimiento : “C”

6.       Creamos precendencias para las tareas

7.       Ejecutamos el Paquete, provocamos un error en el crédito,  y vemos que se insertaron parcialmente la información.

8.       Borramos la Tabla

9.       Asignamos al paquete que soporte Transacciones:  TransactionOption=Required

10.   Seleccionamos la 3 Tareas y asignamos TransactionOption= Supported

11.   Para poder usar transacciones necesitamos el Coordinador de Transacciones Distribuidas.  En servicios de Windows lo verificamos.

12.   Corremos nuevamente el paquete y en OUPUT podemos ver el mensaje que nos envía del error y que se inicio el Coordinador de Transacciones Distribuidas.

13.   Agregamos 2 Containers y agregamos los 2 primeros Tareas al primero Container y la tercera al segundo Contenedor

14.   Quitamos la opción TransactionOption=Required del paquete y la asignamos Supported

15.   Asignamos a cada contenedor la propiedad TransactionOption=Required

16.   Ejecutamos el paquete y vemos que funcionan como transacciones independientes.

Ejercicio 8 – Explorando Datos

1.       Crear un paquete llamado DataProfiling

2.       Agregar en Control Flow Data Profiling Task

3.       Crear una nueva Conexión a la base AdventureWorksDW,  crear un análisis de datos Nulos para la tabla DimCustomers.  Se debe crear un nuevo archivo destino .XML

4.       Ejecutarlo y verificar el archivo XML, el cual tiene la estructura de los campos que analizo con la cantidad de datos nulos encontrados.

5.       Tambien se puede abrir el archivo con la aplicación Data Profile Viewer (Nuevo en SQL Server 2008)

6.       Agregamos nuevas Request al DataProfiling  Column Length Distribution,  Column Pattern Profile ,  Column Value Distribution, CandidateKey, Column Statistics. 

7.       Ejecutamos y analizamos una vez más el archivo con el Data Profile Viewer.  Seleccionar las Columnas para ver la Distribucion.  Hacer Doble Click sobre cualquier distribución para ver los registros.

Ejercicio 9 – Check Points

1.       Crear un nuevo Paquete llamado CheckPoints

2.       Agregar una Variable a nivel del paquete de tipo entero llamada “Estado”

3.       Agregar un Script Task.  Editarlo:  Asignar la variable de tipo escritura,  y en Script, incrementar el valor de la variable y regresar el valor.

Dts.Variables("Estado").Value = Dts.Variables("Estado").Value + 1

MsgBox(Dts.Variables("Estado").Value)

4.       Ejecutar el paquete

5.       Copiar y dejar 5 Script Task similares y renombrarlos  Script 1, 2, 3, 4, 5

6.       Agregar un Execute SQL Task, configurarlo para que falle SELECT 1/0.  División por 0

7.       Crear Constraint 1, 2,3, Execute SQL Task, 4, 5

8.       Ejecutar el paquete, ver el valor de la variable una vez que se reinicia.

9.       Aquí vamos a suponer que cada proceso de cada Script dura horas y que falla en uno de ellos,  tendríamos que reiniciar el proceso ??

10.   Configurar el paquete para que soporte checkpoint,  If Exists,  crear el archivo checkpoint con extensión .xml, y  SaveCheckPoints en True.

11.   Luego configurar el Execute SQL Task, para que si falle, falle el paquete,  FailPackageOnFailure = True

12.   Ejecutar el paquete y revisar el achivo xml de checkpoint creado,  verificar el valor de la Variable.

13.   Luego ejecutar nuevamente el paquete y ver donde inicia.

14.   Reparar la división por 0, y ejecutar el paquete ver donde reinicia.

Ejemplo 10 – Manejo de Errores

1.       Crear Tabla Productos, columna Codigo INT con valores permitidos entre 1 – 800

2.       Crear Tabla ProductosProblema Columna Codigo INT

3.       Crear nuevo paquete llamado  EventHandler

4.       Crear Variable llamada “RandVal”,   “Contador” de tipo Integer.

5.       Agregar un Execute SQL Task, para crear el Truncado de Tablas creadas anteriormente.

6.       Agregar For Loop Container y configurarlo:   InitExpression @Contador = 1, EvalExpression @Contador <=2000,  AssingExpression @Contador = @Contador + 1

7.       Agregar un Script Task en el For Lopp Container.  Asignar la variable de RandVal de tipo escritura.

8.       Editar el Script, 

  Dim rand As New Random

  Dim num As Integer = rand.Next(1001)

           Dts.Variables("Variable").Value = num

9.       Agregar un Execute SQL Task dentro del For Loop Container, conectarlo a la base donde creamos las tablas.  INSERT INTO Productos VALUES (@random).  Definir parámetros en Parameter Mapping, y buscamos la variables RandVal y le ponemos el nombre @random.

10.   Ejecutamos el paquete y vemos cuando falla. Verificamos las tablas

11.   En la pestana Event Handlers, buscamos el Execute SQL Task creado anteriormente dentro del For Loop y creamos un controlador de errores para “On Error”

12.   Agregamos un nuevo Execute SQL Task dentro del eventhandler.  Configuramos la inserción en la tabla ProducotsProblema  INSERT INTO ProductosProblema VALUES(@random),  creamos el parámetro.

13.   En las propiedades del For Loop Container buscamos la propiedad MaximumErrorCount y le ponemos una cantidad grande: 99999, hacer lo mismo para el Execute SQL Task dentro del contenedor.

14.   Ejecutamos el paquete y verificamos las tablas