Leyendo ficheros de texto desde SQL Server Integration Services

Leyendo ficheros de texto desde SQL Server Integration Services

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.

image

 

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

image

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ÔÇØ.

image

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├¡.

image

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

image

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

image

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.

image

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

image

 

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

image

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.

image

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

image

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

image

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

image

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

image

Nuestro paquete finalmente quedó así

image

2 Comentarios en “Leyendo ficheros de texto desde SQL Server Integration Services

  1. Hola! Necesito su ayuda.

    Tengo un archivo CSV con 2 columnas: ID y Nombre.
    EJ:
    1,Juan
    2,Pedro
    3,Carlos

    Necesito armar una tabla HTML con estos datos pero dentro de cuerpo de un mail y luego enviarlo. Todo indicaría que tengo que usar Task Scripts, pero todavía no se muy bien como, ya que soy principiante en SSIS.
    Gracias de antemano.

    Carlos.

Deja un comentario

Tu dirección de correo electrónico no será publicada. Los campos obligatorios están marcados con *