Vamos a ver un ejemplo de como leer ficheros de texto desde SQL Server Integration Services. Huyendo un poco de los ejemplos típicos vamos a hacer un paquete de SSIS que corrija la sincronización de los ficheros .srt de subtitulos. Muchos reproductores de video pueden subtitular cualquier contenido multimedia a partir de ficheros de extensión srt. Estos ficheros realmente son texto plano con un formato como este.

Nota: Como podeis ver mi entorno siempre está en Inglés, en mi caso hay dos motivos, primero trabajo en una compañía internacional, en la que el inglés es la lengua “común”. Segundo, es innegable, que en el mundo de la informática el inglés es lo más universal, no me acaban de convencer las traducciones, no porque estén mal sino porque  a fuerza de costumbre no encuentro nada cuando el entorno está en el idioma de Cervantes. Mis disculpas a los más puristas.

1
00:00:01,968 –> 00:00:04,835
Ejemplo de texto 1

2
00:00:04,938 –> 00:00:08,237
Ejemplo de texto 2

En resumen se trata de una linea indicando el orden, otra indicando el intervalo de tiempo separado por –> y una tercera que es el texto. Nuestro ejemplo tratará de crear un paquete de SSIS que cambie (adelantando o atrasando) la sincronización de estos ficheros .srt.

Para comenzar lo que haremos será crear un nuevo paquete de SSIS. Los ejemplos que vais a ver están hechos con la CPT3 de SQL Server 2012 , AKA Denali.

Colocaremos un Data flow Task. En entorno nuevo es realmente muy atractivo, el zoom se ve muy claramente, las barras de tareas están muy mejoradas, incluso la ayuda parece más adecuada al comienzo con SSIS

Renombraremos la caja del data flow task con un nombre más descriptivo, recordad que esto es bastante importante. Si lo hacemos bien los paquetes serán prácticamente auto-documentados y eso será realmente interesante, particularmente transcurrido algún tiempo desde el comienzo.

Despues, pulsaremos botón derecho sobre la superficie de Connection Managers y crearemos una nueva conexión a fichero de texto pulsando sobre “New Flat File Connection”.

Ahí simplemente especificaremos un fichero del formato que estamos tratando, en mi caso demo.crt como puede observarse en la siguiente figura. Notese que he tocado la propiedad “column names in the first data row” porque no es así.

En nuestro caso las columnas como tal no existen, pero si tendremos 4 tipos de filas, los casos más normales quizá los campos vengan separados por comas o por puntos y comas, o por cualquier caracter. Cualquier escenario común se podrá manejar tocando unas pocas propiedades. Nuestro caso, en el que vienen mezcladas cosas hemos de probar a distinguir cada una de las lineas. Al pulsar en la segunda pestaña veremos una pantalla como la siguiente

Esto nos dice que efectivamente cada línea tendremos que tratarla de una forma distinta. A continuación vamos a poner un conector a este fichero de texto como origen de datos siguiendo el wizard de SQL Server 2012

En nuestro caso seguiremos el wizard, elegiremos fichero de texto plano y el origen de datos que acabamos de crear.  Eso nos dejará un componente origen de datos de texto  que veremos en las siguientes imágenes. En nuestro caso además le cambiaremos el nombre, ya sabéis con el objeto de que sea autodescriptivo.

En nuestro caso, lo único que queremos hacer es modificar las líneas que tienen fecha y horas desplazandolas un tiempo hacia adelante o hacia atrás.

Las columnas que tienen este dato tienen un formato como este

00:00:01,968 –> 00:00:04,835

Ambas horas han de ser desplazadas.

Para poder hacer el desplazamiento necesitaremos dos cosas

• Identificar que es una línea de tipo rango de tiempos
• Añadir o quitar el tiempo determinado

Para poder añadir o quitar un tiempo variable, usaremos eso.. variables. Vamos pues a añadir una variable “desplazamiento”.  Para ello pulsaremos con el botón derecho del mouse sobre la superficie del dataflow task y pulsaremos la opcion “variables”. A continuación pulsaremos el botón añadir variable , que es el resaltado en amarillo en la siguiente figura.

Nombraremos a la variable como estimemos oportuno (en nuestro ejemplo desplazamiento) y le daremos como tipo de datos int y como Valor el desplazamiento que queremos obtener (milisegundos) . El objeto debe tener como Scope (alcance) el necesario para que funcione en nuestro ambiente. El scope es el equivalente a donde se ven las variables (todo el paquete, este objeto, el flujo de trabajo.. etc). Nuestra variable ha de quedar como puede verse en la siguiente figura.

El siguiente paso será distinguir las líneas que son de tipo desplazamiento. Si lo es, entonces simplemente sustituiremos el valor por el incremento que necesitamos en ambos casos.

Para distinguir estos casos usaremos la funcion FindString, que localiza la posición de una ocurrencia. Esta condición

FINDSTRING([Column 0],”–>”,1) == 0

Nos daría verdadero solamente si la cadena no se encuentra. Si la cadena se encuentra entonces tendremos que incrementar la función.

Para incrementar desplazamiento sobre la fecha hora hay que hacer lo siguiente :

• Separar la hora de comienzo
• Convertirla en fecha hora
• Incrementarla
• Convertirla en texto
• Separar la hora de fin
• convertirla en fecha hora
• incrementarla
• convertirla en texto
• Unirla

Todas estas operaciones se podrían hacer en una sola expresión, pero esto seguramente nos haría muy compleja esa expresión, en su lugar, y con el fin de mejorar la claridad lo vamos a hacer en varios pasos

Paso 1. Crear dos columnas con el texto desde hasta.

Para desde usaremos la siguiente expresión

FINDSTRING([Column 0],”–>”,1) != 0 ? LEFT([Column 0],FINDSTRING([Column 0],”–>”,1) – 1) : “00:00:00.000″

Entendida significa,

si la posición que devuelve findstring es distinta de cero, (es una de las que nos interesan)

Empezando por la izquierda quedate los caracteres hasta que empieza el “—>”

si no

pon un “00:00:00.000”

Un caso equivalente es el de la expresión hasta

FINDSTRING([Column 0],”–>”,1) != 0 ? SUBSTRING([Column 0],FINDSTRING([Column 0],”–>”,1) + 3,100) : “00:00:00.000″

sla posición que devuelve findstring es distinta de cero, (es una de las que nos interesan)

Empezando por donde está el “—>” quedate con todos los caracteres hasta el final

si no

pon un “00:00:00.000”

Así queda el derived column component que tiene estas expresiones

Ambas expresiones devuelven un string.. y no se puede operar con esos strings como si fueran fechas, así que vamos a añadir un componente adicional que convierta a fecha esos strings y les añada el desplazamiento

La formula que usaremos será la siguiente  para desde

DATEADD(“Ms”,@[User::Desplazamiento],(DT_DBTIMESTAMP)(DT_DBTIME2,3)REPLACE(Desde,”,”,”.”))

Es decir en un solo paso convertirmos a fecha, añadimos los milisegundos que necesitamos y guardarmos en una nueva variable

El motivo de que la expresión sea tan compleja es para asegurarnos que no tenemos molestos problemas de conversión. Pasando por DT_DBTIME2 nos aseguramos de convertir adecuadamente el formato que recibimos “00:00:00,000”, como la coma que recibimos esperamos sea un punto.. pues hay que sustituirla. Despues convertimos a D_DBTIMESTAMP para poder hacer la operación de suma de milisegundos y por último sumamos esos milisegundos.

La figura muestra como queda la cajita de derived column

Conseguido esto ahora hemos de deshacer el camino, deshacer el camino implica convertir esto a un formato tal que así .

00:00:01,968 –> 00:00:04,835

Aunque también podría hacerse en un solo paso, vamos a usar varios para que se vea con más claridad. La expresión que usaremos será la siguiente

SUBSTRING((DT_WSTR,30)[Desde Fecha],12,12)

Primero convertimos a string, esto nos da el formato 2011-10-25 00:00:15.988 y de este nos quedamos con 12 caracteres empezando en la posición 12, es decir.. el mismo formato de origen. Así queda el cuadro derived column.

Por último usaremos una nueva columna derivada para unir ambas tal y como estaba la original, la fórmula será, y aprovecharemos para sustituir la actual con esta expresión en el caso que sea la columna que esperamos.

FINDSTRING([Column 0],”–>”,1) != 0 ?  [Desde Texto] + ” –> ” + [Hasta Texto] :  [Column 0]

Así queda el componente

Por último, grabamos nuestro fichero de salida, para eso creamos una nueva conexion a archivos de texto y seguimos el asistente como destino.

Configuramos el fichero de la misma forma, excepto que en advanced añadiremos nosotros la columna de forma manual como string de 200 caracteres. Despues en los destinos buscaremos el flat file dstination y lo enchufaremos para que funcione. Entraremos en mappings y conseguiremos mapear la columna 0 del origen a la columna 0 del destino

Por último vamos a permitir que tanto el fichero de origen como el de destino puedan ser expresados por parámetros, usando variables. Así pues crearemos dos variables, origen y destino de tipo string

Estas variables nos serviran para ponerlas como expresión en las cadenas de conexion. Lo que haremos será poner en expresiones que el connection string se tome desde una variable, como puede verse en la imagen siguiente

Haremos la misma operación con el destino, quedará tal que así

Nuestro paquete finalmente quedó así

Cuando hay un gran número de datos por sincronizar entre tablas es muy común usar estrategias para disminuir el número de filas afectadas. TSQL puede servir, incluso si hay dos servidores, podemos llevar una tabla al lugar remoto, hacer un full join, o usar el comando MERGE, pero.. suele ser una solución que escala poco y que carga el server.

Curiosamente esta misma estrategia, usando SSIS puede ser muy muy ágil. Básicamente por la forma en la que SQL Server Integration Services va a manejar los datos, usando buffers que va a ir liberando progresivamente. Lo que vamos a ver durante el artículo es una parte bastante básica de SSIS, aún así es un modelo que puede ser perfectamente extrapolable a muchísimas situaciones y puede ser realmente ágil y util, veamos lo que diga.

Lo que vamos a usar en SQL Server Integration Services va a ser un DataFlow o flujo de datos. Dentro de él, tendremos dos componentes que leerán las tablas de origen y destino, un componente que hará el join (en nuestro caso haremos full join) y un conditional split que nos permitirá distinguir que tipo de comportamientos encontramos en la tabla. O hablando de forma mas clara, al final lo que vamos a hacer es leer el origen y el destino, comparar los registros y  seguir los siguientes criterios:

• Si un registro está en origen y no en destino… es un registro nuevo.
• Si un registro está en destino y no en el origen … es un registro borrado. (alguna vez estuvo.. ya no)
• Si un registro está en ambos sitios, hay que comparar los datos, si son iguales, no hay nada que hacer y si no lo son entonces hay que actualizar el registro en destino.

Sobre este proceso que lee todo hay mil mejoras que pueden hacerse si se tiene control sobre como pasan las cosas en el origen. Aspectos como CDC (Change Data Capture), Triggers en origen, tablas de log que puedan dejar las aplicaciones pueden cambiar sensiblemente este planteamiento y pueden además hacerlo mucho mas eficiente. En cualquier caso, lo que vamos a escribir en este artículo es la versión más sencilla, fuerza bruta,leer y sincronizar todo.

El esquema global del paquete será el siguiente

Lo primero que haremos será poner un data flow, al que cambiaremos el nombre por “Sincronización de XX” en nuestro caso  Sincroniza Dbo_dim_customers. Las descripciones no admiten algunos caracteres especiales, sin embargo quiero recalcar la importancia de poner nombres muy descriptivos, siempre he dicho que los paquetes de SSIS pueden considerarse “Auto documentados” si están bien estructurados, y además dejan mucho más claro lo que hacen y son más mantenibles, así pues… invertir (que no perder) unos segundos en poner nombres descriptivos me parece que es ganar mucho tiempo global en el desarrollo.

Dentro de este  flujo de datos en donde vamos a sincronizar nuestra tabla, comenzaremos añadiendo dos conexiones a nuestro Connection manager, tal y como puede verse en la siguiente figura. La imgaen puede resultar un pcoo engañosa, yo he renombrado después los objetos de conexión para que me resulten más fácilmente identificables. Comenzando por Origen y Destino, de esta forma me resulta mucho más sencillo.

 En amarillo resaltado podeis ver que los origenes han sido renombrados a Origen AdventureWorks y DestinoAdventureworks.

Tras eso hemos arrastrado un OLE Db Source que hemos llamdo ORIGEN Dim Customer, que va a contener un SELECT a la TABLA dbo.DimCustomer que es la que vamos a sincronizar.  Una de las cosas que mas complica los paquetes de SSIS de este tipo es que los nombres de columnas en origen y destino suelen ser iguales, con lo que luego el paquete internamente los diferencia añadiendo delante como prefijo el nombre del objeto que los “creó″ dentro de SSIS, esto da nombres larguísimos y muchas posibilidades de equivocarse, así que yo suelo añadir un alias siempre, para origen ORIGEN_<nombre de campo> y para destino igual DESTINO_<nombre de campo>, sin embargo en tablas como esta o mayores en número de columnas (27) esta operación puede ser un poco pesada, yo lo que suelo hacer es usar un truquito, la siguiente sentencia, para que los nombres de las columnas se me escriban “solas”, esto y un poquito de copiar y pegar arreglan muchas cosas

 SELECT COLUMN_NAME +' as ORIGEN_'+COLUMN_NAME +', ' from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME='DimCustomer'

En nuestro caso el resultado obtenido tras todas esas operaciones de copiar y pegar es el siguiente

SELECT CustomerKey as ORIGEN_CustomerKey, GeographyKey as ORIGEN_GeographyKey, CustomerAlternateKey as ORIGEN_CustomerAlternateKey, Title as ORIGEN_Title, FirstName as ORIGEN_FirstName, MiddleName as ORIGEN_MiddleName, LastName as ORIGEN_LastName, NameStyle as ORIGEN_NameStyle, BirthDate as ORIGEN_BirthDate, MaritalStatus as ORIGEN_MaritalStatus, Suffix as ORIGEN_Suffix, Gender as ORIGEN_Gender, EmailAddress as ORIGEN_EmailAddress, YearlyIncome as ORIGEN_YearlyIncome, TotalChildren as ORIGEN_TotalChildren, NumberChildrenAtHome as ORIGEN_NumberChildrenAtHome, EnglishEducation as ORIGEN_EnglishEducation, SpanishEducation as ORIGEN_SpanishEducation, FrenchEducation as ORIGEN_FrenchEducation, EnglishOccupation as ORIGEN_EnglishOccupation, SpanishOccupation as ORIGEN_SpanishOccupation, FrenchOccupation as ORIGEN_FrenchOccupation, HouseOwnerFlag as ORIGEN_HouseOwnerFlag, NumberCarsOwned as ORIGEN_NumberCarsOwned, AddressLine1 as ORIGEN_AddressLine1, AddressLine2 as ORIGEN_AddressLine2, Phone as ORIGEN_Phone, DateFirstPurchase as ORIGEN_DateFirstPurchase, CommuteDistance as ORIGEN_CommuteDistance FROM dbo.DimCustomer

El resultado es  el que podeis ver en las dos siguientes figuras

Es la configuración, exceptuando el error, del componente oleDB que nos sirve de origen, lo más interesante de las imágenes es que como puede verse el alias indica claramente que estamos hablando de los datos de origen, de forma que no hay duda, estemos a la altura del flujo de datos que estemos de donde provienen estos campos y resultan menos probables errores por este punto.

De la misma forma configuramos el destino que quedará de una forma muy similar a lo que hemos visto en el origen, pero simplemente con los alias cambiados.

Vamos a configurar un poco más el componente. Básicamente son SELECTs, sin embargo, es conveniente que estos datos vengan ordenados. El motivo de esta ordenación es que cuando se va a hacer un merge join  las entradas han de estar ordenadas y el componente sort de SSIS es un componente bloqueante, lo que no quiere decir que no se use, simplemente que si se puede evitar sin perjudicar mucho al origen mejor. En estos casos suele ser un orden por la clave primaria, y eso suele ser muy poco costoso para los origenes, pero si que ganamos muchísima velocidad de ejecución. Para eso tenemos que asegurarnos que SSIS sabe que los datos vienen ordenados, no basta con ordenarlos. Para eso seguiremos los siguientes pasos, sobre el origen de datos (origen y destino, claro), botón derecho, mostrar editor avanzado, y ahí lo primero asegurarnos que el flujo de datos marca que está ordenado.

Además de eso hay que entrar en las columnas y especificar cual de ellas ocupa la posición 1 de orden, la posición 2 etc. Es decir si el conjunto de registros viene ordenado por más de un campo hay que indicar el orden. Esto se hace como puede verse en la siguiente figura.

Una vez tenemos estos dos componentes configurados y ordenados (recordad que hay que hacerlo para ambos componentes) 

A continuación tenemos que añadir el compnente merge Join y unirlo a ambas entradas. También le cambiaremos el nombre para describir su cometido.

Despues entramos a configurarlo señalando que es un full outer join y seleccionando todas las columnas.  El componente quedará como podemos  ver en la figura siguiente.

A continuación hemos de distinguir entre los registros nuevos, los registros borrados y los registros modificados.  Este punto es lo que contabamos en el origen más desarrollado

• Los registros nuevos serán aquellos que estando en origen no están en destino, lo que podremos comprobar mirando si la clave en destino es NULL (será porque no ha hecho match y por tanto deja Destino a NULL).
• Los registrosborrados serán aquellos que estando en destino ya no se encuentran en origen, esto lo podemos comprobar mirando si la clave de origen es null (no ha hecho match con el origen). 

Ambos casos podeis verlos implementados en la siguiente pantalla.

Lo que esto no soluciona es el problema de la comparación. comparar no es que sea complicado pero si es… pesado, hay 27 campos y la linea de condition del componente conditional split no es que sea precisamente el sitio indicado para escribir cantidad de código. Una alternativa es escribir código personalizado en un componente, no es demasiado complicado y hay asunciones que podemos hacer si hemos seguido la estructura (campo ORIGEN_ se corresponde con otro DESTINO_) pero aún así, no me atrae esa idea, prefiero de alguna forma escribirlo aquí. Otro problema adicional son los nulos, ningun lenguaje, ni siquiera transact SQL sabe comparar nulos, tiene su explicación, pero el caso es que nosotros debemos hacer es que ambos sean nulos o que sean iguales. No nos vale sustituir el valor nulo por un valor por defecto, porque si algún campo tuviera valor por defecto y se cambia por nulo podriamos perder el cambio. Así pues lo haremos en varios pasos.

• Primero comparamos los valores que no son NULABLES, es decir valores que no pueden contener un nulo. En este caso no hay que preocuparse por los nulos
• Luego comparamos aquellos campos que en origen sean nulos y en destino no. De esta forma detectamos los cambios entre nulos
• Por último aquellos que son distintos, pero para que no falle en ningún caso sustituimos los nulos por valores por defecto.  

 Para poder teclear todo esto pondremos un conditional split tal y como vemos en la siguiente figura.

El código que pondremos dentro será  el que se ve en la siguiente figura, y que para conseguirlo, en lugar de teclear he usado los comandos que veis más abajo como código.

Para los distintos no anulables :

select '( ORIGEN_'+COLUMN_NAME +' !=  DESTINO_'+COLUMN_NAME +')  || '
from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME='DimCustomer'  
AND IS_NULLABLE ='NO'

Para que uno de los campos valga nulo y el otro cualquier otra cosa

select '((ISNULL(ORIGEN_'+COLUMN_NAME +')  && ! ISNULL(DESTINO_'+COLUMN_NAME +'))  ||  '+
'(!ISNULL(ORIGEN_'+COLUMN_NAME +')  &&  ISNULL(DESTINO_'+COLUMN_NAME +')))  ||  '
from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME='DimCustomer'  
AND IS_NULLABLE ='YES'

Finalmente, para los casos en que haya alguna diferencia :

select '( ( ISNULL(ORIGEN_'+COLUMN_NAME +') ? ' + 
    case WHEN DATA_TYPE in ('int','money','tinyint','numeric') then ' 0 '
         WHEN DATA_TYPE in ('datetime','smalldatetime') then '(DT_DATE)"1990-01-01"  '
         WHEN DATA_TYPE in ('char','varchar','nchar','nvarchar') then '""' 
         WHEN DATA_TYPE in ('bit') THEN ' false' END +
  ' : ORIGEN_' + COLUMN_NAME +')  !=   (ISNULL(DESTINO_'+COLUMN_NAME +') ? ' + 
    case WHEN DATA_TYPE in ('int','money','tinyint','numeric') then ' 0 '
         WHEN DATA_TYPE in ('datetime','smalldatetime') then '(DT_DATE)"1990-01-01"  '
         WHEN DATA_TYPE in ('char','varchar','nchar','nvarchar') then '""' 
         WHEN DATA_TYPE in ('bit') THEN ' false' END +
   ': DESTINO_' +COLUMN_NAME + ' ) ) || '
from INFORMATION_SCHEMA.COLUMNS c where TABLE_NAME='DimCustomer'  
AND IS_NULLABLE ='YES'

Una vez que tenemos todos los casos registrados hemos de comenzar a tratar cada uno de ellos, el primer caso es el de las inserciones de registros nuevos. No hay muchos considerandos, cuanto más rápido se inserten mucho mejor, sin embargo el modo por defecto del componente OLE-DB Destination produce table lock, y eso obviamente impide cualquier otra operación en la tabla, incluso de nosotros mismos. Por tanto podemos caer en bloquearnos a nosotros mismos, y eso además de bastante incordiante es un poco de lelos, así pues evitémoslo.  Para evitarlo, quitemos el table-lock (aunque haga que las inserciones sean un poquito más lentas) y también es útil a veces cambiar los parámetros de rows per batch, que indican cuantos registros tiene cada bloque insertado y maximunt insert commit size que indica de que tamaño es el máximo insert (por defecto a un número ridiculamente enorme).  Yo suelo poner bloques de 10.000 o 1.000 filas si no hago ningún estudio. En casos más complejos en los que algunos insert puedan fallar por otros motivos (integridad referencial o cualquier otra cosa) lo que se hace es poner un tamaño pequeño ya que esos bloques pueden tratarse posteriormente registro a registro para quedarte en un lugar exclusivamente con los que producen los errores. Este tipo de tratamiento está totalmente fuera del alcance de este artículo.

La segunda pestaña muestra el mapeo entre entradas y salidas, en el común de los casos no hace falta tocarlo, porque al final simplemente se liga por nombre, pero en nuestro caso hemos añadido el prefijo ORIGEN_ por lo tanto el link no puede hacerse de forma automatica, yo simplemente me pongo en primero y pulso la letra O,  dos veces tab y otra vez O y así de forma mecánica. como el orden de los campos es el mismo, lo normal es que en unos segundos esté todo linkado y bien linkado

En el caso que nos ocupa así quedan configuradas las entradas.

No hemos tocado  nada en la pestaña de Error output.

La siguiente salida que vamos a tratar va a ser la de elementos eliminados. En nuestro caso vamos a implementar un borrado. Realmente esto no es tan sencillo, un borrado físico, habiendo integridad referencial puede ser un tema muy complejo, además se hace fila a fila y eso puede perjudicar el rendimiento si se produjesen muchos borrados. Hay más alternativas y mejoras, pero desde luego esta es una opción a la hora de tratar eliminados sin excedernos en complejidades.

En las siguientes imágenes podeis ver como queda el componente OLE DB command que servirá para el borrado. Notad como el valor que tomamos para la clave es Destino_CustomerKey, esto es debido a que en esta salida el origen viene como NULL.

Por último nos queda que hacer las modificaciones a los registros existentes. Esto implica unir tres ramas distintas , para lo que usaremos un componente union all  despues de ese componente haremos el update, para lo que necesitamos un comando update  del tipo Update TABLA SET CAmpo =?, CAmpo2=?,… CAmpoN=? where CamposClave=? and…

Este comando puede simularse de forma parecida a la que hemos hecho con las escrituras para el conditional split, dejo del lado del lector que lo haga . 

En las siguientes lineas podeis ver como queda configurado el comando update

En proximos articulos veremos las pruebas que le vamos a hacer a este paquete para comprobar su buen funcionamiento.

Podeis descargar el paquete de ejemplo de aquí mismito

En el video podéis ver el como se hizo del ejemplo del primer tutorial. Disculpad mi acento y los ruiditos que se oyen, como esto no es TV sino solamente un ejemplo no he editado todo para que parezca totalmente profesional.. También tendría que haberme hecho una operación de cambio de voz en fin.. sin enrollarme, aquí teneis el link

Introducción

El flujo de control de los paquetes de Integration services siven para orquestar todos los pasos que hay que ejecutar para consolidar la información. Son el auténtico WorkFlow de los procesos de extracción transformación y carga. Cualquier operación que tengamos que hacer para consolidar información, para extraer información de un origen y llevarla a un destino tendrá una serie de pasos, esos pasos se orquestarán mediante los flujos de control.

Ejemplos:

Renemos que descargar un conjunto de ficheros e incorporar cada uno de ellos a una base de datos, despues de incorporarlo si el fichero se incorporó por completo irá al directorio de completados y si falló algo al de incorporados con error. Esta funcionalidad se implementa usando tareas ftp que vayan a descargar el fichero y lo dejen en un directorio. Despues usaremos un bucle para recorrer todos los ficheros y llamar a su incorporación y una tarea en caso de acierto que lleve el fichero al directorio adecuado y otra que lo haga en caso de fallo al directorio de erroneos.

Tareas que hay que hacer en secuencia. Por ejemplo, primero hacemos un backup de una base de datos, despues reorganizamos índices, chequeamos integridad y hacemos otra copia.   O bien, consolidamos los datos a un datawarehouse, despues procesamos las dimensiones de un cubo y despues sus grupos de medida.

Algunos elementos del flujo de control

Cuando abrimos el entorno de Bussinnes Intelligence Development Studio, nos aparece lo primero el flujo de control. La barra de herramientas es la que está orientada al flujo de contro. Además de los componentes que podemos pinchar y arrastrar, es importante conocer las formas en las que podemos unir cada una de las cajitas, que operación hará que vayamos por un lado del flujo o por el otro.

Una categoría de objetos que resulta muy interesante de tratar son los Contenedores , en el gráfico de arriba podemos ver los For Loop Container, Foreach Loop Container, Sequence Container.

Sequence Cotainer.Este componente no es más que un contenedor de otros componentes, pero sirve tanto para paralelizar procesos (que varias cajitas se ejecuten a la vez) como para  que un conjunto de procesos solo se ejecuten si se ha producido un evento anterior o bajo determinadas circunstancias. Veremos ejemplos a lo largo de este artículo.  .

For Loop Container. Es un bucle al estilo de la programación normal en el que se especifica la clausula de inicialización, la cláusula de evaluación (detiene el bucle cuando devuelve False, es por tanto una expresión booleana) y la clásula de asignación, que sirve para cambiar los valores y que la expresión booleana  que se evalua pueda ir cambiando. En resumen es equivalente a cualquier bucle de un entorno de programación normal.

Foreach Loop Container. Este bucle recorre elementos de una colección, la colección pueden ser desde ficheros en un directorio hasta registros en un dataset. Dependiendo del tipo que se haya seleccionado.

File System Task. Esta tarea nos va a servir para ilustrar nuestro primer ejemplo. Es una tarea que sirve para hacer operaciones con archivos, copiar, mover, eliminar, etc. En la siguiente tabla podemos ver las opciones que tiene para con los ficheros y por tanto que tareas podemos hacer.

En cualquier caso lo que pretendemos con este capítulo no es enumerar los posibles objetos, para eso ya teneis los libros en pantalla que los explican incluso mejor que lo que humildemente yo pueda hacer aquí. Lo que pretendo es que aprendais un uso concreto del flujo de control e ilustrar este a través de ejemplos. Creo que con ejemplos es como mas se aprende.

Primer Ejemplo

En este primer ejemplo vamos a ver como usar un componente ForEach para recorrer los archivos de un directorio y como obtener el nombre de ese fichero y trabajar con él en una tarea.

Lo primero que vamos a hacer es arrastrar un componente ForEach y modificar su mensaje (para ello pulsamos F2) Yo particularmente soy de los partidarios de que los nombres sean descriptivos y ahorrar en documentación, Creo que así, si queda todo autoexplicado es mucho má sencillo de mantener que tener al lado montañas de documentación de los proyectos que tienen una tendencia especial a quedarse automáticamente obsoletas (eso si no son hechas cuando ya no hacen falta simplemente para cubrir el expediente).

La caja en cuestión tiene una serie de propiedades, primer punto  de atención, si estás acostumbrado a Visual Studio igual tienes la tentación de pulsar F4, bien hecho, pero los componente de SSIS tienen un método más sencillo de ser configurados (por norma general) que se obtiene haciendo doble click en la cajita en cuestión en lugar de pulsando la tecla F4. También vale pulsar con el botón derecho del ratón y elegir la opción Editar para conseguir el mismo efecto.

En nuestro caso el Bucle ForEach tiene 4 pestañas de configuración que son General, Collection, Variable Mappings y expressions. Sobre el tema de expressions ya iremos hablando, así que os pondré los pantallazos de como quedan las otras tres en nuestro caso y os comentaré a continuación que conseguimos con estos formatos (los dejaremos para este ejemplo pero habrá que ir cambiandolos para los sucesivos.

En la primera pestaña tenemos el nombre de objeto y su descripción, la parte importante viene en el segundo y tercer lugar. En la  segunda pestaña podemos ver que es un bucle para recorrer ficheros en un directorio, cual es el directorio, que tipo de ficheros queremos recorrer y como queremos que nos devuelva el nombre del fichero (Full qualified quiere decir que con el path completo).  Por ultimo hay un check por si queremos recorrer subdirectorios.  En la tercera pestaña podemos ver que nos va a devolver en una variable (que hemos creado desde el propio asistente lo que el objeto tenga en la posición 0, que os adelanto que es el nombre completo del archivo que ha leido.

Con esta configuración ya podemos leer los ficheros y saber como se llaman. Si ejecutasemos ahora mismo nuestro paquete, se nos pondría en verde diciendo que ha terminado correctamente, sin embargo, a mi me gustaría que en este primer paso, al menos comprobasemos que  ha funcionado, y para ello se me ocurre que podemos añadir una tarea de modificación de ficheros y cambiarle el atributo a nuestros archivos por ejemplo a solo lectura.

Para ello hemos de arrastrar dentro del contenedor la tarea de File System y configurarla para que el fichero lo tome de nuestra variable y además que sea una operación de tipo cambio de atributos (lo que cambiará las propiedades que hemos de rellenar). En nuestro caso la configuración quedará como sigue :

Las flechas azules indican las cosas que hemos cambiado, y el resultado de la ejecución es que los ficheros quedan con el atributo cambiado a solo lectura. Podeis descargar el ejemplo aquí (ponedlo en c:\ para que funcione sin tocar).

Dentro del tutorial de SQL Server Integration Services he pensado ir poniendo videos con pequeñas demos. Este video es nivel 100, es decir que no espereis nada realmente complicado ni espectacular, es un mero paseo por el entorno, dando una idea ligera sin ningún detalle de los componentes que nos vamos a encontrar, aunque sin profundizar mucho en ninguno de ellos. Poco a poco iremos complicando las demostraciones y los temas, de momento, sin embargo, estamos con las introducciones. Espero que sea lo que andais buscando.

Introducción

SQL Server Integration services es un producto que viene incluido con la licencia de Microsoft SQL Server y que es la propuesta de Microsoft para lo que se llama Extracción, Transformación y Carga de datos, y yo me atrevería a decir que también para la limpieza de los mismos. Muchas veces creemos que el lenguaje obvio para hacer todas estas tareas es T-SQL, sin embargo, la experiencia me dice que cuando de lo que se trata es de consolidar información y de moverla muy rápido, nada como SSIS para resolver esa cuestión.

SQL Server Integration services es un producto en el que se desarrolla de forma muy visual, básicamente pinchando y arrastrando componentes desde la barra de herramientas hasta los lugares de diseño, cada uno de esos componentes admitirá parametrización para adaptarlo a nuestras necesidades. 

Un vistazo rápido

Para comenzar con SSIS, hemos de instalar las herramientas de Cliente de SQL Server, en ellas nos aparecera el BIDS (Bussiness Intelligence Developmen Studio),  dentro de los proyectos de Bussiness Intelligence tenemos los proyectos de Integration Services, como puede observarse en esta figura

Una vez que seleccionamos el proyecto de Integration services se nos abre un paquete en blanco. Los paquetes están separados en dos partes fundamentales, los flujos de control y los flujos de datos.

En el flujo de control especificaremos las tareas que se han de ir haciendo y las dependencias y restricciones. En esa superficie podemos colocar las tareas de flujo de datos, y dentro configurarlas para que muevan los datos de un sitio a otro.

Algunas de las tareas que podremos hacer con SQL Server Integration services de forma sencdilla serán:

• Recorrer listas de ficheros para incorporarlos a nuestras bases de datos
• Procesar cubos, dimensiones o ejecutar comandos XMLA
• Insertar datos de forma masiva
• Extraer, limpiar, transformar y cargar datos en datamarts o datawarehouses
• Usar tareas para entender mejor como es la distribución de nuestros datos
• Ejecutar comandos T-SQL
• Mover, copiar o eliminar archivos
• Obtener o poner datos en un servidor FTP
• Leer o escribir datos en colas de Microsoft Message Queue
• Enviar correos electrónicos con los resultados de las operaciones
• Transferir objetos SQL
  • Logins
  • Jobs
  • Procedimientos de master
  • Objetos de servidor
• Invocar a servicios Web XML
• Leer datos WMI o suscribirse a eventos WMI
• Tareas de mantenimiento de bases de datos
  • Backups
  • Chequeos de integridad
  • Tareas del agente SQL
  • Tareas TSQL
  • Limpieza de hisotircos
  • Tareas de limpieza de datos
  • Notificaciones a operadores
  • Reorganización y reconstrucción de índices
  • Purgado de bases de datos
  • Actualización de Estadisticas

Todas estas operaciones pueden realizarse en un servidor distinto de nuestros motores de bases de datos, usando al máximo los recursos de la máquina y por lo tanto con una gran capacidad de escalado y un altísimo rendimiento.

Dentro de las tareas que podremos hacer con datos en sí también podemos usar alguna de las siguientes:

• Origenes de datos
  • Leer datos con proveedores  de datos .NET
  • Leer datos de archivos Excel
  • Leer datos de ficheros planos, estén formados practicamente como estén formados
  • Leer datos de cualquier fuente que ofrezca un proveedor OLEDB, (todos los fabricantes del mercado lo hacen)
  • Leer datos en formato “crudo” raw
  • Leer datos en formatos XML
• Transformaciones de datos
  • Agregar información
  • Guardar y usar datos en caché para evitar leer la misma información más de una vez
  • Añadir información de traza a los datos (nombre del paquete, id de ejecución , versión, hora de comienzo…
  • Realizar transformaciones sencillas de textos (pasar a mayúsculas o minúsculas, cambiar a lenguajes chinos, disminuir tampaños,…)
  • Mandar datos a un lugar o a otro en función de valores
  • Copiar columnas
  • Convertir tipos de datos
  • Completar la información con consultas a modelos de minería de datos
  • Obtener columnas con cálculos complejos
  • Guardar o leer archivos BLOB convirtiendolos en ficheros y vicebersa
  • Usar ténicas de lógica difusa tanto para agrupados como para búsquedas
  • Combinar datos
  • Realizar Joins complejos
  • Crear copias en memoria de los datos para añadir comportamientos
  • Ejecutar comandos SQL contra conexiones oldb
  • Obtener muestreos de datos por porcenaje,
  • Conteos diversos
  • Trasposición de matrices (Pivot)
  • Ordenar
  • Extracción y busqueda de terminos
  • Asistentes para dimensiones lentamente cambiantes
• Destino de datos
  • Escribir datos con proveedores de datos .NET
  • Escribir datos a archivos Excel
  • Escribir datos a ficheros planos
  • Entrenar modelos de mineria de datos
  • Escribir datos datos en cualquier destino que ofrezca un proveedor OLEDB, (todos los fabricantes del mercado lo hacen)
  • Escribir datos en formato crudo
  • Devolver Recorsets, y Datareaders
  • Mandar datos a SQL Server Compact

Todas estas funcionalidades se ven acompañadas además por un a gran versatilidad en la configuración, en la trazabilidad de las ejecuciones , en la firma digital de paquetes y en el uso y paso de variables para poder parametrizar adecuadamente el producto.

En resumen estamos ante un producto que ofrece un sin fín de posibilidades que iremos desgranando en los siguientes capítulos de este tutorial que nos llevará buena parte del año escribir por completo.