En el mundo de la arquitectura de desarrollo SSIS, la preferencia debería darse a extraer datos desde archivos planos en lugar de bases de datos relaciones no Microsoft. Esto es porque usted a menudo tiene que preocuparse acerca del soporte de controladores y problemas de compatibilidad en su máquina de desarrollo/servidor SSIS que a menudo se atribuyen a proveedores de bases de datos no Microsoft. De hecho, he estado en muchas situaciones donde no podíamos actualizar a otra versión de SSIS (por ejemplo, BIDS a SSDT) debido a problemas de compatibilidad del controlador del proveedor externo en la nueva versión de SSIS.
Aunque la preferencia debería darse a importar archivos planos, debe notarse que cuando quiera que sea posible, el formato plano deseado debería ser delimitado. Esto es porque los archivos apropiadamente delimitados reducen el tiempo gastado en la configuración del mapeo de columnas, ya que SSIS puede detectar delimitadores de filas y columnas. Puede que no se dé cuenta del beneficio de tener a SSIS mapeando las columnas automáticamente por usted hasta que se siente con un dataset de ancho fijo que tenga que dividir manualmente en 100+ columnas de salida. La Figura 1 ilustra un escenario de mundo perfecto de un archivo de transacción ficticia Fruit Sales que tiene filas separadas por avance de página/avance de línea y columnas delimitadas por una barra vertical (también conocido como Canalizador).
Desafortunadamente, como desarrolladores ETL, a menudo tenemos que extraer datos desde un mundo imperfecto que tiene archivos planos formateados en todo tipo de formas. La Figura 2 ilustra otra representación de nuestro dataset ficticio Fruits Sales con SSIS incapaz de detectar delimitadores de columnas. Cuando sea que se encuentre con tales archivos, usted está limitado a otras dos opciones en SSIS y que es o tratarlos como formateados con Ancho Fijo o Formato Derecho Irregular. El objetivo de este artículo es explorar diferentes maneras de trabajar con la última opción, Formato Derecho Irregular.
Definiendo el Formato Derecho Irregular
Ya sea que está importando archivos planos usando SQL Server Integration Services (SSIS) o SQL Server Management Studio (SSMS), la opción de formato derecho irregular en el menú desplegable en ambas herramientas puede ser encontrada al final de la lista como se muestra en la Figura 3. Esto no debería confundirse ya que el orden preferido del formato de archivo es nada más que una lista alfabética de los formatos de archivo disponibles. De hecho, yo creo firmemente que la opción de Formato Derecho Irregular es realmente un híbrido entre los formatos de ancho Delimitado y Fijo. A diferencia de los archivos delimitados, donde los delimitadores de columna y fila son requeridos, en el formato Derecho Irregular sólo los delimitadores de fila son requeridos, en el formato Derecho Irregular sólo los delimitadores de fila son auto detectados con el contenido de las filas almacenadas dentro de una sola columna. Si desea dividir la fila en muchas columnas, entonces usted tiene que especificar el límite de columna manualmente. Esto es contrario al formato de ancho Fijo, donde usted no debería sólo especificar los límites de columna, sino que los delimitadores de fila también.
Configurando Columnas
La configuración de columnas es quizá una parte crítica de todo el proceso ETL, ya que nos ayuda a construir metadatos para su ETL. De hecho, sin importar si SSIS/SSMS puede detectar delimitadores o no, si usted salta la sección Column Mapping – su ETL fallará la validación. Para clarificar cómo los archivos con formato Derecho Irregular funcionan, he ido un paso atrás y usé la Figura 4 para realmente mostrar una vista previa de nuestro dataset de transacciones ficticio Fruits desde Notepad++. Puede ser ya visto desde Notepad++ que el archivo sólo tiene delimitador de fila en una forma de CRLF.
Cuando el archivo mostrado en la Figura 4 es importado a SSIS se ve como en la Figura 5 e inmediatamente el delimitador de fila es automáticamente detectado.
En este punto, podemos lidiar con la configuración de columna en una de 2 maneras:
Mapeo de Columna Individual
El mapeo de una columna individual está disponible por defecto cuando se trabaja con el formato Derecho Irregular. Este mapeo por defecto es el más simple, y sin embargo también el más proclive a error. Es más simple porque usted no tiene gastar tiempo dividiendo las filas de datos en dos o más columnas. De todos modos, tal enfoque usualmente resulta en un error de truncado de Texto, similar al mostrado en la Figura 6.
Figura 6: Error de Truncado de Texto El error de truncado de texto es uno de los errores más comunes que posiblemente se encontrará cuando importe archivos. No importa si usted está lidiando con archivos con Delimitador, de ancho Fijo o de formato Derecho Irregular. En archivos planos, el tamaño de la columna nunca es dinámico, lo que significa que cuando usted configuró previamente su conexión de tal manera que [Column 0] tenga un tamaño de 5, pero usted posteriormente carga un archivo con un tamaño de 6, usted obtendrá un error de truncado.
La manera más fácil de evitar errores de truncado es añadiendo masa al tamaño de su columna (por ejemplo, si usted sabe que el tamaño de su columna dada es 75, hágalo 100).
Otra desventaja de mapear todas las filas contra una columna individual es que para dividir los valores de las filas en muchas columnas, usted tendrá que conducir transformaciones adicionales (ya sea dentro de SSIS usando tareas de transformación o en la base de datos SQL Server usando T-SQL).
Por ejemplo, el script en la Figura 7 muestra mucha lógica de sub cadenas T-SQL que fue usada para dividir valores desde la columna individual ([Column 0]) a columnas separadas.
Figura 7: Transformación T-SQL de una Columna Individual Los resultados de la ejecución del script en la Figura 7 son mostrados en la Figura 8.
Figure 8: Execution Result of Single Column T-SQL Transformation Script Mapeos de Columna Múltiples
Una alternativa al mapeo de una columna individual es usar marcadores para especificar muchos límites de columna. Poe ejemplo, viendo nuestro dataset ficticio dado en la Figura 4, podemos adivinar muchas cabeceras de columnas. Por tanto, al principio de cada fila, los primeros dos caracteres se ven como números de elementos, mientras que los siguientes 6 caracteres se ven como una fecha de transacción seguida por lo que parecen nombres de frutas.
El resto del límite de la columna se ve como en la Figura 9.
Figura 9: Múltiples Límites de Columna El mayor beneficio de intentar especificar el límite de la columna es que cuando se trata de transformación de datos, tenemos menos manipulación de datos comparado con el mapeo de una columna individual. La Figura 10 muestra que no tuvimos que manipular las primeras tres columnas porque sabíamos qué representaban. En lugar de eso, nos enfocamos en dividir datos desde [Column 3].
Figura 10: Transformación Múltiple de Datos de Límites de Columnas Justo como con el mapeo de columnas individuales, especificar un límite de columna tiene sus propias desventajas. Por ejemplo, si el tamaño de los valores que son mapeados contra [Column 2]) súbitamente se incrementa, algunos de esos valores serán llevados a las siguientes columnas (si no hay columnas subsecuentes, un error de truncado será lanzado). La Figura 11 muestra un dataset ficticio revisado con nombres de Banana en las filas 9-10, renombrado a SIFISOBananas. A diferencia de antes, [Column 2] ahora puede almacenar el nombre de fruta extendido, de este modo causando una división en el nombre de la fruta con los últimos tres caracteres (por ejemplo, nas) siendo almacenado en [Column 3].
Figura 11: Tamaño Incrementado del Valor Esperado
Conclusión
En este artículo establecimos que a menudo no se nos presentan archivos planos formateados apropiadamente. En lugar de eso, tenemos que lidiar con archivos planos de formato Derecho Irregular, a saber, esos datos de fila podrían ser mapeados contra una columna individual o a través de múltiples columnas.
Descargue el material de scripts/demostración de este artículo aquí.
Referencias:
- Asistente de Importación y Exportación de SQL Server
- Administrador de Conexión de Archivos Planos
- Sintaxis de Sub cadenas T-SQL
Él es un miembro del Grupo de Usuarios de SQL de Johannesburgo y tiene un grado de maestría en MCom IT Management de la Universidad de Johannesburgo.
Actualmente, él trabaja para Sambe Consulting como Consultor principal.
Ver todas las publicaciones de Sifiso W. Ndlovu
- Entendiendo el Impacto de las sugerencias NOLOCK y WITH NOLOCK en SQL Server - May 28, 2018
- Cómo reemplazar caracteres especiales ASCII en SQL Server - May 25, 2018
- Convertir resultados de SQL Server a JSON - June 2, 2017